SELECT

Synopsis

[(] SELECT [%NOFPLAN] [%NOLOCK]
    [DISTINCT [BY (item {,item})] | ALL] 
    [TOP {int | ALL}]
    select-item {,select-item}
    [INTO host-variable-list]
    [FROM [optimize-option] table-ref [[AS] t-alias]
      {,table-ref [[AS] t-alias]} ]
    [WHERE condition-expression]
    [GROUP BY scalar-expression]
    [HAVING condition-expression]
    [ORDER BY item-order-list [ASC | DESC] ]
[)]

select-item ::= 
  [t-alias.]*   |
  [t-alias.]scalar-expression [[AS] c-alias]
    {,[t-alias.]scalar-expression [[AS] c-alias]}

概要

SELECT 文は、Caché データベースのデータを検索するクエリを実行します。最も単純な形式では、1 つまたは複数の項目を単一のテーブルから検索します。それらの項目は select-item リストで指定し、テーブルは FROM table-ref 節で指定します。さらに複雑なクエリでは、SELECT は複数のテーブルでデータを検索したり、ビューを使用してデータを検索できます。

SELECT は、SQL 関数、ホスト変数またはリテラルから値を返す場合にも使用できます。SELECT クエリは、これらのデータベース以外の値を返すことと、テーブルまたはビューでデータを検索することを組み合わせることができます。そのようなデータベース以外の値を返すことのみに SELECT を使用する場合は、FROM 節はオプションです。詳細は、"FROM" 節を参照してください。

SELECT クエリから返される値を結果セットと呼びます。ダイナミック SQL では、SELECT は取得した値を %SQL.StatementOpens in a new tab クラスに格納します。"Caché SQL の使用法" の "ダイナミック SQL" の章、および "インターシステムズ・クラス・リファレンス" の "%SQL.StatementOpens in a new tab" クラスを参照してください。

Caché は、SELECT の成功もしくは失敗を示す SQLCODE ステータス変数を設定します。また、SELECT 操作を行うと、%ROWCOUNT ローカル変数に選択された行数が設定されます。通常、SELECT 操作が成功すると、SQLCODE=0 が設定され、%ROWCOUNT には選択された行数が設定されます。単純な SELECT を含む埋め込み SQL の場合、選択されるデータは多くても 1 行であるため、SQLCODE=0 が設定され、%ROWCOUNT は 0 か 1 が設定されます。しかし、埋め込み SQL の SELECT で、カーソルを宣言し複数行からデータをフェッチする場合、処理はカーソルがデータの最後に達した場合に完了し (SQLCODE=100)、この時点で選択された全行数が %ROWCOUNT に設定されます。詳細は "FETCH" コマンドを参照してください。

DISTINCT 節

DISTINCT キーワード節は、フィールド値の重複を取り除きます。以下の 2 つの形式があります。

• SELECT DISTINCT：select-item の値の一意な組み合わせごとに 1 行を返します。1 つまたは複数の select-items を指定できます。例えば、以下のクエリでは、Home_State および Age の値の一意な組み合わせごとに、Home_State および Age の値を含む行が返されます。

  SELECT DISTINCT Home_State,Age FROM Sample.Person

  

• SELECT DISTINCT BY (item)：item の値の一意な組み合わせごとに 1 行を返します。1 つの item 、またはコンマ区切りのリストで複数の item を指定できます。select-item リストに、任意で、指定した item (1 つまたは複数) を含めることができます。例えば、以下のクエリでは、Home_State および Age の値の一意な組み合わせごとに、Name および Age の値を含む行が返されます。

  SELECT DISTINCT BY (Home_State,Age) Name,Age FROM Sample.Person

  

  item には、アスタリスクを除く任意の有効な select-item 値を指定できます。列名エイリアスを指定することはできません。

どちらのタイプの DISTINCT 節でも、一意性をテストする項目を複数指定できます。複数の項目をリストすると、すべての項目の組み合わせに個別な行がすべて取得されます。DISTINCT では、NULL を一意の値とは見なしません。詳細は、"DISTINCT 節" のリファレンス・ページを参照してください。

TOP 節

TOP キーワード節は、SELECT 文で返す行数を指定の数に限定します。返された仮想テーブルの “一番上” から、指定された行数分を返します。既定では、テーブルのどの行が “上位” にあるかは予想できません。しかし、Caché は、DISTINCT 節および ORDER BY 節が指定された場合、TOP 行を選択する前にそれらを適用します。詳細は、"TOP 節" のリファレンス・ページを参照してください。

Select-item

これは、すべての SELECT 文の必須要素です。一般に、select-item は、FROM 節で指定されたテーブル内のフィールドを参照します。select-item は、以下の 1 つ以上の項目で構成されます。複数の項目の場合はコンマで区切られます。

• 列名 (フィールド名)。テーブル名のエイリアスがあるものとないものがあります。

  SELECT Name,Age FROM Sample.Person

  

  フィールド名は、大文字と小文字が区別されません。ただし、結果セット内のフィールドに関連するラベルでは、select-item に指定された大文字/小文字ではなく、テーブル定義に指定された SqlFieldName の大文字/小文字が使用されます。大文字/小文字の解決の詳細は、“フィールド列のエイリアス” を参照してください。

  指定したテーブルのために定義されているすべての列名をリストするには、"Caché SQL の使用法" の “テーブルの定義” の章の "列の名前と番号" を参照してください。

  RowId (レコード ID) を表示するには、%ID 疑似フィールド変数エイリアスを使用できます。これは割り当てられている名前に関係なく、RowId を表示します。既定では、RowId の名前は ID ですが、ID というユーザ定義フィールドがある場合、Caché ではその名前を変更することができます。既定では、RowId は非表示フィールドです。

  ストリーム・フィールドでの SELECT は、開いているストリーム・オブジェクトの oref (オブジェクト参照) を返します。

  SELECT Name,Picture FROM Sample.Employee WHERE Picture IS NOT NULL

  

  FROM 節で 1 つまたは複数のテーブルまたはビューを指定するとき、次の 2 つの例に示すように、ピリオドを使用して、select-item の一部としてテーブル名 (またはテーブル名エイリアス) を含める必要があります。

  完全テーブル名 ：

  SELECT Sample.Person.Name,Sample.Employee.Company
        FROM Sample.Person, Sample.Employee

  

  テーブル名エイリアス ：

  SELECT p.Name, e.Company
        FROM Sample.Person AS p, Sample.Employee AS e

  

  ただし、エイリアスがそのテーブル名に割り当てられている場合、完全なテーブル名を select-item の一部として使用することはできません。これを行おうとすると、SQLCODE -23 エラーになります。

  照合関数を使用して select-item フィールドのソートや表示を指定できます。照合関数は括弧なし (SELECT %SQLUPPER Name) でも括弧付き (SELECT %SQLUPPER(Name)) でも指定できます。照合関数で切り捨てを指定する場合には括弧が必要です (SELECT %SQLUPPER(Name,10))。

  select-item が埋め込みオブジェクト・プロパティ (埋め込みシリアル・クラス・データ) を参照する場合、下線構文を使用します。下線構文は、オブジェクト・プロパティ (コンテナ) の名前、アンダースコア、および埋め込みオブジェクト内のプロパティ名で構成されています。例えば、Home_City や Home_State です(例えばインデックス・テーブルなど他のコンテキストでは、これらはドット構文 Home.City を使用して記述されます)。詳細は、"Caché オブジェクトの使用法" の “コレクション・プロパティの SQL プロジェクションの制御” を参照してください。

  SELECT Home_City,Home_State FROM Sample.Person

  

  SELECT を使用して、下線構文を使用するのではなく、コンテナ・フィールドに直接照会することができます。返されるデータはリスト形式のため、$LISTTOSTRING 関数または $LISTGET 関数を使用してデータを表示できます。例えば以下のようになります。

  SELECT $LISTTOSTRING(Home,'^') AS HomeAddress FROM Sample.Person

  

• サブクエリ。サブクエリは、指定されたテーブルから 1 つの列を返します。この列は、単一テーブル・フィールドの値 (SELECT Name) か、連結の使用 (SELECT Home_City||Home_State) またはコンテナ・フィールドの指定 (SELECT Home) によって単一列として返された複数テーブル・フィールドの値となります。サブクエリでは、矢印構文を使用できます。サブクエリでは、サブクエリ内で引用されたテーブルに 1 つのデータ・フィールドしかない場合でも、アスタリスク構文は使用できません。

  サブクエリの一般的な使用法の 1 つは、GROUP BY 節の影響を受けない集約関数の指定です。以下の例では、GROUP BY 節によって、年齢を 10 年ごとにグループ化します (例えば、25 才から 34 才)。AVG(Age) select-item は、GROUP BY 節で定義される各グループの平均年齢となります。全グループ内のすべてのレコードの平均年齢を取得するために、サブクエリを使用します。

  SELECT Age AS Decade,
         COUNT(Age) AS PeopleInDecade,
         AVG(Age) AS AvgAgeForDecade,
         (SELECT AVG(Age) FROM Sample.Person) AS AvgAgeAllDecades
  FROM Sample.Person
  GROUP BY ROUND(Age,-1)
  ORDER BY Age

  

• FROM 節のテーブル以外のテーブルのフィールドにアクセスするための矢印構文。これは、暗黙結合として知られています。以下の例では、Sample.Employee テーブルには、Sample.Company テーブル内の対応する会社名のための行 ID が含まれている Company フィールドがあります。矢印構文では、そのテーブルから会社名を取得します。

  SELECT Name,Company->Name AS CompanyName
        FROM Sample.Employee

  

  この場合、参照されるテーブルに対する SELECT 特権 (参照されるフィールドと、参照されるテーブルの RowID 列の両方に対する、テーブルレベルの SELECT 特権、または列レベルの SELECT 特権) が必要です。矢印構文の詳細は、"Caché SQL の使用法" の "暗黙結合 (矢印構文)" を参照してください。

• 列番号順でテーブル内のすべての列を選択する、アスタリスク構文 (*)。

  SELECT TOP 5 * FROM Sample.Person

  

  アスタリスク構文では、非公開のフィールドはリストされません。既定では、RowId は非表示です (SELECT * では表示されません)。ただし、%PUBLICROWID を指定してテーブルが定義されている場合は、 SELECT * は RowId フィールドおよび非公開でないすべてのフィールドを返します。既定では、このフィールドの名前は ID ですが、ID というユーザ定義フィールドがある場合、Caché ではその名前を変更することができます。

  Note:

  Samples ネームスペースで提供されるテーブルの例は、そのほとんどが %PUBLICROWID で定義されています。

  アスタリスク構文では、その他の非公開フィールドもリストされません。例えば、Sample.Person の Home フィールドおよび Office フィールド (これらは、Sample.Address 埋め込みシリアル・クラス内の埋め込みオブジェクト・プロパティを参照する) は、SELECT * では表示されません。

  select-item がアスタリスクであり、複数のテーブルが指定されている場合には、結合されたすべてのテーブルのすべての列が選択されます。

  SELECT TOP 5 * FROM Sample.Company,Sample.Employee

  

  アスタリスク構文は、修飾、未修飾のどちらでもかまいません。select-item がテーブル名 (またはテーブル名エイリアス) およびピリオド (.) を接頭語として使用することによって修飾される場合、select-item は、指定されたテーブル内のすべての列を選択します。修飾されたアスタリスク構文は、別のテーブルの別の選択項目と結合できます。

  以下の例では、select-item は、テーブルからすべての列を選択する未修飾のアスタリスク構文で構成されます。重複する列名 (この例では Name) および列でない select-item 要素 (この例では {fn NOW}) も指定できます。

  SELECT TOP 5 {fn NOW} AS QueryDate,
               Name AS Client,
               *
  FROM Sample.Person

  

  以下の例では、select-item は、1 つのテーブルからすべての列を選択する修飾されたアスタリスク構文と、別のテーブルの列名のリストで構成されます。

  SELECT TOP 5 E.Name AS EmpName, 
               C.*, 
               E.Home_State AS EmpState
  FROM Sample.Employee AS E, Sample.Company AS C

  
  Note:

  SELECT * は、アプリケーションの開発およびデバッグ中に非常に便利な Caché SQL を完全にサポートしている部分です。しかし、稼動中のアプリケーションでは、好まれるプログラミングの実践は、アスタリスク構文形式を使用するよりも、明示的に選択したフィールドを一覧表示することです。明示的にリストしたフィールドは、アプリケーションをわかりやすく簡単に理解し、簡単に維持し、名前によってフィールドを簡単に検索できるようにします。

• 1 つまたは複数の SQL 集約関数を含む select-item。集約関数は、常に単独の値を返します。集約関数の引数は、以下のいずれかになります。

  • 単独の列名 — クエリによって選択された行の NULL でないすべての値に対して、集約を計算します。

    SELECT AVG(Age) FROM Sample.Person

    

  • 集約の計算にはスカラ式も利用可能です。

    SELECT SUM(Age) / COUNT(*) FROM Sample.Person

    

  • アスタリスク構文 (*) — テーブルの行数を計算するために、COUNT 関数と共に使用されます。

    SELECT COUNT(*) FROM Sample.Person

    

  • 選択個別関数 — 余剰値を消去することで、集約を計算します。

    SELECT COUNT(DISTINCT Home_State) FROM Sample.Person

    

  • ANSI SQL は単独の SELECT 文内での列名と集約関数の組み合わせを許可しませんが、Caché SQL は以下を許可することにより、標準を拡張しています。

    SELECT Name, COUNT(DISTINCT Home_State) FROM Sample.Person

    

  • %FOREACH を使用する集約関数。これによって、単独または複数の列の各個別の値に対して集約関数が計算されます。

    SELECT DISTINCT Home_State, AVG(Age %FOREACH(Home_State)) 
         FROM Sample.Person

    

  • %AFTERHAVING を使用する集約関数。これによって、HAVING 節で指定されるサブ母集団で集約関数が計算されます。

    SELECT Name,AVG(Age %AFTERHAVING) 
          FROM Sample.Person
          HAVING (Age > AVG(Age))

    

    以上の文は、平均年齢よりも年齢が高いレコードを返し、データベースの全員の平均年齢よりも高齢な人の平均年齢を返します。

• プロシージャとして格納されているユーザ定義のクラス・メソッド。非修飾メソッド名または修飾メソッド名のいずれかにすることができます。既定の SQLUser スキーマに存在する RandLetter()、修飾されたクラス・メソッド名の Sample.RandLetter()、およびクラス・メソッド "Rand_Letter"() を呼び出す Sample.Rand_Letter() は、すべて有効なクラス・メソッド名です。以下の例で、RandCaseLetter() は、大文字 (‘U’) または小文字 ('L') でランダムな文字を返すクラス・メソッドです。

  SELECT RandCaseLetter('U')

  

  メソッドからの戻り値は、論理形式から表示/ODBC 形式に自動的に変換されます。メソッドへの入力値は、既定では、表示/ODBC 形式から論理形式には変換されません。ただし、入力の表示形式から論理形式への変換は、$SYSTEM.SQL.SetSQLFunctionArgConversion()Opens in a new tab メソッドを使用してシステム全体について構成できます。$SYSTEM.SQL.GetSQLFunctionArgConversion()Opens in a new tab を使用すると、このオプションの現在の構成を判断できます。

  指定されたメソッドが現在のネームスペース内に存在していない場合、SQLCODE -359 エラーが生成されます。指定されたメソッドがあいまいな (複数のメソッドを参照する可能性がある) 場合、SQLCODE -358 エラーが生成されます。クラス・メソッドの作成の詳細は、"CREATE METHOD" を参照してください。

• データベースの列で動作するユーザ指定の ObjectScript 関数呼び出し (外部関数)。

  SELECT $$REFORMAT^ABC(name)FROM MyTable
  
  

  

  MySQL
    &sql(SELECT Name,$$MyFunc() INTO :n,:f FROM Sample.Person)
    WRITE "name is: ",n,!
    WRITE "function value is: ",f,!
    QUIT
  MyFunc()
    SET x="my text"
    QUIT x

  

  このオプションをシステム全体で構成した場合のみ、SQL 構文内でユーザ指定 (外部) 関数を呼び出すことができます。管理ポータルに進み、システム, 構成, 一般SQL設定 を選択します。[SQL 文で外部関数の使用を許可] の現在の設定を表示して編集します。既定値は “いいえ” です。既定では、ユーザ指定関数を呼び出そうとすると、SQLCODE -372 エラーが発生します。

  ユーザ指定関数を使用して、% ルーチン (% 文字で始まる名前を持つルーチン) を呼び出すことはできません。実行しようとすると SQLCODE -373 エラーが発行されます。

• フィールド値に追加処理を適用する select-item。

  算術演算 ：

  SELECT Name, Age,Age-AVG(Age) FROM Sample.Person

  

  select-item の算術演算に除算が含まれており、データベース内のそのフィールドに、除数の値がゼロまたは NULL になるような値がある場合には、ゼロでの除算を避けるためにテストの順序に頼ることはできません。その代わり、CASE 文を使用してリスクを抑制します。

  SQL 関数 ：

  SELECT Name,$LENGTH(Name) FROM Sample.Person

  

  SQL 大文字/小文字変換関数 ：

  SELECT Name,UCASE(Name) FROM Sample.Person

  

  XMLELEMENT、XMLFOREST、または XMLCONCAT 関数。これらは、指定された列名から検索したデータの値を XML (または HTML) タグで囲みます。詳細は "XMLELEMENT" を参照してください。

• すべてのレコードに対して同じ値を返す select-item。

  すべての select-items がテーブル・データを参照しない場合、FROM 節はオプションです。FROM 節を含める場合には、指定したテーブルが存在している必要があります。オプションの FROM 節の詳細は、"FROM" 節のリファレンス・ページを参照してください。

  • 算術演算 ：

    SELECT 7 * 7, 7 * 8 FROM Sample.Person

    

    SELECT Name, Age, 9 - 6 FROM Sample.Person

    

  • 文字列リテラル、または文字列リテラルで動作する関数。

    SELECT UCASE('fred') FROM Sample.Person

    

    以下の例に示すとおり、結果がよりわかりやすくなるように、文字列リテラルを使用できます。

    SELECT TOP 10 Name,'was born on',%EXTERNAL(DOB)
    FROM Sample.Person

    

    数値リテラルをどのように指定するかによって、そのデータ型が決まります。したがって、文字列 '123' はデータ型 VARCHAR と報告され、数値 123 はデータ型 INTEGER または NUMERIC と報告されます。

  • %TABLENAME または %CLASSNAME 疑似フィールド変数キーワード。%TABLENAME は、現在のテーブル名を返します。%CLASSNAME は、現在のテーブルに対応するクラスの名前を返します。クエリが複数のテーブルを参照する場合には、キーワードの接頭語としてテーブルのエイリアスを指定できます。例えば、SUB_ACCESSIBLE_FILE のように指定します。

  • 以下のいずれかの ObjectScript 特殊変数 (またはそれらの省略形) ： $HOROLOG、$JOB、$NAMESPACE、$TLEVEL、$USERNAME、$ZHOROLOG、$ZJOB、$ZNSPACE、$ZPI、$ZTIMESTAMP、$ZTIMEZONE、$ZVERSION。

列のエイリアス

select-item を指定する場合、AS キーワードを使用して列名のエイリアスを指定できます。

SELECT Name AS PersonName, DOB AS BirthDate, ...

列のエイリアスは、結果セット内の列見出しとして表示されます。列のエイリアスの指定はオプションであり、既定では必ず提供されます。列のエイリアスは、指定した大文字/小文字区別で表示されます。ただし、ORDER BY 節で参照される場合には大文字/小文字は区別されません。c-alias 名は有効な識別子である必要があります。c-alias 名は区切り識別子でもかまいません。詳細は、"Caché SQL の使用法" の “識別子” の章を参照してください。

AS キーワードは必須ではありませんが、これにより、クエリ文字列が読みやすくなります。そのため、以下も有効な構文です。

SELECT Name PersonName, DOB BirthDate, ...

SQL は、列エイリアスのための一意性チェックは実行しません。フィールド列と列エイリアスの名前を同じにすることや、2 つの列エイリアスを同じにすることができます (ただし、推奨されていません)。このように、列のエイリアスが一意でないと、ORDER BY 節によって参照されたときに SQLCODE -24 “[ソートカラムがあいまいです]” エラーが発生する場合があります。列エイリアスは、すべての SQL 識別子と同様に、大文字と小文字を区別しません。

他の SELECT 節での列エイリアスの使用は、クエリのセマンティック処理順序によって制御されます。列を参照するには、ORDER BY 節でその列エイリアスを使用します。選択リスト、DISTINCT BY 節、WHERE 節、GROUP BY 節、または HAVING 節にある、別の select-item 内の列エイリアスを参照できません。JOIN 操作の ON 節または USING 節内で列エイリアスを参照することはできません。ただし、"Caché SQL の使用法" の “データベースの問い合わせ” の章で示されているように、 サブクエリを使用すると、他のこれらの SELECT 節で列エイリアスを使用できるようになります。

フィールド列のエイリアス

select-item フィールド名は大文字と小文字が区別されません。ただし、列エイリアスを指定する場合以外は、結果セットのフィールド列名は、列プロパティに関連付けられた SqlFieldName の大文字/小文字に従います。SqlFieldName の大文字/小文字は、select-item リストの指定ではなく、テーブル定義で指定されたフィールド名と一致します。そのため、SELECT name FROM Sample.Person では、フィールド列ラベルが Name として返されます。フィールド列エイリアスを使用すると、以下の例のように、表示する大文字/小文字を指定できます。

SELECT name,name AS NAME
FROM Sample.Person

大文字/小文字の解決には時間がかかります。SELECT パフォーマンスを最大化するには、テーブル定義の指定に従って、フィールド名と同じ大文字/小文字を指定します。ただし多くの場合、テーブル定義のフィールドの大文字/小文字の判別は、不便で、誤りが発生しやすい点があります。その代わりに、フィールド列エイリアスを使用して大文字/小文字の問題を回避することができます。フィールド列エイリアスへのすべての参照の大文字/小文字を一致させる必要があることに注意してください。

以下のダイナミック SQL の例では、大文字/小文字の解決が必要です (SqlFieldNames は “Latitude” と “Longitude”)。

  ZNSPACE "SAMPLES"
  SET myquery = "SELECT latitude,longitude FROM Sample.USZipCode"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WHILE rset.%Next() {WRITE rset.latitude," ",rset.longitude,! }

以下のダイナミック SQL の例は大文字/小文字の解決が不要なため、より早く実行されます。

  ZNSPACE "SAMPLES"
  SET myquery = "SELECT latitude AS northsouth,longitude AS eastwest FROM Sample.USZipCode"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WHILE rset.%Next() {WRITE rset.northsouth," ",rset.eastwest,! }

t-alias テーブル・エイリアス接頭語は、列名には含まれません。したがって、以下の例では、両方の列に Name というラベルが付けられます。

SELECT p.Name,e.Name
FROM Sample.Person AS p LEFT JOIN Sample.Employee AS e ON p.Name=e.Name

複数テーブルを指定するクエリ内の列を区別するには、列のエイリアスを指定する必要があります。

SELECT p.Name AS PersonName,e.Name AS EmployeeName
FROM Sample.Person AS p LEFT JOIN Sample.Employee AS e ON p.Name=e.Name

データを理解しやすくするために、列エイリアスを指定することもできます。以下の例では、テーブル列 “Home_State” は、“US_State_Abbrev” という名前に変更されます。

SELECT Name,Home_State AS US_State_Abbrev
FROM Sample.Person

%ID は特定の列を参照するため、以下の例に示すとおり、フィールド名 (既定では ID)、または指定された列エイリアスを返すということを覚えておいてください。

SELECT %ID,%ID AS Ident,Name
FROM Sample.Person

フィールド以外の列のエイリアス

フィールド以外の列には、自動的に列名が割り当てられます。このようなフィールドに対してエイリアスを指定しない場合、Caché SQL は、"Expression_1" または "Aggregate_3" などの一意の列名を提供します。整数の接尾語は、SELECT 文で指定されている select-item の位置を参照します。それらは、そのタイプのフィールドのカウントではありません。

以下に、自動的に割り当てられる列名を示します (n は整数です)。

• Aggregate_n: 集約関数。例えば、AVG(Age) や COUNT(*) です。1 番外側の操作が集約関数の場合は、その集約に式が含まれている場合であっても、列の名前は Aggregate_n になります。例えば、COUNT(Name)+COUNT(Spouse) は Expression_n になりますが、MAX(COUNT(Name)+COUNT(Spouse)) は Aggregate_n になります。

• HostVar_n: ホスト変数。これは、リテラル (‘text’、123、空の文字列 ('') など)、入力変数 (:myvar)、またはリテラルで置き換えられる ? 入力パラメータ になります。数字への記号の追加、文字列の連結、算術演算など、リテラルに対する式評価によって Expression_n になるということに注意してください。? パラメータに指定されたリテラル値は、式評価なしで、変更されないまま返されます。例えば、5+7 を指定すると、文字列 '5+7' が HostVar_n として返されます。

• Literal_n: %TABLENAME や NULL 指定子のような疑似フィールド変数。%ID は Literal_n ではないことに注意してください。これには実際の RowID フィールドの列名が付けられます。

• Subquery_n: 1 つの select-item を指定するサブクエリの結果。select-item は、フィールド、集約関数、式、またはリテラルにできます。サブクエリ内ではなく、サブクエリの後に列のエイリアスを指定します。

• Expression_n：リテラル、フィールド上の select-item リストまたは Aggregate_n、HostVar_n、Literal_n、または Subquery_n select-item で何らかの操作を行うと、列名が Expression_n に変更されます。これには、単項演算 (-Age)、算術演算 (Age+5)、連結 ('USA:'||Home_State)、データ型 CAST 操作、SQL 照合関数 (%UPPER(Name))、SQL スカラ関数 ($LENGTH(Name))、ユーザ定義のクラス・メソッド、CASE 式、および特殊変数 (CURRENT_DATE や $ZPI など) が含まれます。

以下の例では、AVG 関数で作成された集約フィールド列には、列エイリアス “AvgAge” が与えられます。既定の名前は “Aggregate_3” (SELECT リストの位置 3 の集約フィールド) です。

SELECT Name, Age, AVG(Age) AS AvgAge FROM Sample.Person

以下の例は、AS キーワードがここで省略されていることを除き、前と同じです。このキーワードの使用は推奨されますが、必須ではありません。

SELECT Name, Age, AVG(Age) AvgAge FROM Sample.Person

以下の例では、select-item サブクエリのための列エイリアスを指定する方法を示します。

SELECT Name AS PersonName,
       (SELECT Name FROM Sample.Employee) AS EmpName,
       Age AS YearsOld
FROM Sample.Person

FROM 節

FROM table-ref 節は、1 つまたは複数のテーブル、ビュー、テーブル値関数、またはサブクエリを指定します。これらの table-ref タイプの任意の組み合わせを、コンマ区切りのリストとしてや、JOIN 構文で指定できます。単一の table-ref を指定する場合、指定されたデータはそのテーブルまたはビューから取得されます。複数の table-ref を指定する場合、SQL はテーブルに対して結合オペレーションを実行し、そのデータを指定されたデータの取得元の結果テーブルにマージします。

複数の table-ref を指定する場合、コンマまたは明示的な結合構文キーワードを使用してこれらのテーブル名を区切ることができます。コンマで区切られたテーブル名のリストの指定法の詳細は、"FROM" 節のリファレンス・ページを参照してください。明示的な JOIN 構文 (RIGHT JOIN や *= など) で複数のテーブル名を指定する方法の詳細は、"JOIN" のリファレンス・ページを参照してください。

$SYSTEM.SQL.TableExists()Opens in a new tab メソッドまたは $SYSTEM.SQL.ViewExists()Opens in a new tab メソッドを使用して、テーブルまたはビューが現在のネームスペースに存在するかどうかを確認することができます。$SYSTEM.SQL.CheckPriv()Opens in a new tab メソッドを使用して、そのテーブルまたはビューの SELECT 特権を保有しているかどうかを確認することができます。

テーブルのエイリアス

table-ref を指定するときには、AS キーワードを使用して、そのテーブル名またはビュー名のエイリアスを指定できます。

FROM Sample.Person AS P

AS キーワードは必須ではありませんが、これにより、クエリ文字列が読みやすくなります。以下は有効な同義の構文です。

FROM Sample.Person P

t-alias 名は有効な識別子である必要があります。t-alias 名は区切り識別子でもかまいません。t-alias は、クエリ内のテーブル・エイリアス間で一意である必要があります。すべての識別子などの t-alias では、大文字/小文字は区別されません。そのため、大文字/小文字の区別のみが異なる 2 つの t-alias 名を指定することはできません。これにより、SQLCODE -20 “[名前が重複しています]” エラーが発生します。詳細は、"Caché SQL の使用法" の “識別子” の章を参照してください。

テーブル・エイリアスは、フィールドが属するテーブルを示すために、フィールド名の接頭語 (ピリオド付き) として使用されます。例えば以下のようになります。

SELECT P.Name, E.Name
FROM Sample.Person AS P, Sample.Employee AS E

クエリで同一フィールド名が存在する複数のテーブルを指定する場合は、テーブル参照の接頭語を使用する必要があります。テーブル参照の接頭語としては、t-alias (上記参照) を使用することや、以下の同義の例で示すように完全修飾テーブル名を使用することができます。

SELECT Sample.Person.Name, Sample.Employee.Name
FROM Sample.Person, Sample.Employee

ただし、t-alias がそのテーブル名に割り当てられている場合、完全なテーブル名を select-item の一部として使用することはできません。これを行おうとすると、SQLCODE -23 エラーになります。

クエリで 1 つのテーブル (またはビュー) しか参照しない場合は、テーブル・エイリアスの指定は任意となります。クエリで複数のテーブル (および/またはビュー) を参照し、参照されるフィールド名が各テーブル固有の名前である場合は、テーブル・エイリアスの指定は任意となります (ただし、推奨されています)。クエリで複数のテーブル (および/またはビュー) を参照し、参照されるフィールド名が別テーブル内のフィールド名と同一の場合は、テーブル・エイリアスの指定は必須となります 。t-alias (または完全修飾テーブル名) 接頭語の指定に失敗すると、SQLCODE -27 “[該当テーブル内でフィールド '%1' があいまいです]” エラーが発生します。

以下のようなサブクエリを指定する際、t-alias を使用できますが、必須ではありません。

SELECT Name,(SELECT Name FROM Sample.Vendor)
FROM Sample.Person

t-alias は、クエリ実行のためにしかフィールドを一意に識別できません。クエリ結果セットの表示のためにフィールドを一意に識別するには、列エイリアス (c-alias) を使用する必要もあります。以下の例では、テーブル・エイリアス (Per および Emp) および列エイリアス (PName および Ename) を両方使用します。

SELECT Per.Name AS PName, Emp.Name AS EName
FROM Sample.Person AS Per, Sample.Employee AS Emp
WHERE Per.Name %STARTSWITH 'G'

名前が競合することなく、フィールド、列エイリアス、および/またはテーブル・エイリアスのために同じ名前を使用できます。

t-alias 接頭語は、参照されているテーブルを区別する必要がある箇所ならどこでも使用できます。以下に、この例をいくつか示します。

SELECT P.%ID As PersonID,
       AVG(P.Age) AS AvgAge,
       Z.%TABLENAME||'=' AS Tablename,
       Z.*
FROM Sample.Person AS P, Sample.USZipCode AS Z
WHERE P.Home_City = Z.City
GROUP BY P.Home_City
ORDER BY Z.City 

WHERE 節

WHERE 節は、クエリ選択から特定の行を適格とするか、もしくは不適格とします。適格な行とは、condition-expression が True である行です。condition-expression は、AND および OR 論理演算子によってリンク可能な論理テスト (述語) のリストです。これらの述語は、単項否定論理演算子を使用して反転できます。

SQL の述語は以下のカテゴリに分類されます。

• 比較述語

• BETWEEN 述語

• LIKE 述語

• NULL 述語

• IN および %INLIST 述語

• EXISTS 述語

• FOR SOME 述語

• FOR SOME %ELEMENT 述語

詳細は、"WHERE" 節のリファレンス・ページを参照してください。condition-expression に集約関数を含めることはできません。集約関数によって返される値を使用して選択条件を指定したい場合は、HAVING 節を使用してください。

WHERE 節は、= (内部結合)、 =* (左外部結合)、および *= (右外部結合) シンボル結合演算子を使用して、2 つのテーブルの間の明示的な結合を指定できます。詳細は、このドキュメントの "JOIN" のページを参照してください。

WHERE 節は、矢印構文 (–>) 演算子を使用して、ベース・テーブルと別のテーブルのフィールドとの明示的な結合を指定できます。詳細は、"Caché SQL の使用法" の"暗黙結合" を参照してください。

GROUP BY 節

GROUP BY 節はクエリの結果行をとり、単独または複数のデータベース列によって結果行を個別のグループに分割します。SELECT を GROUP BY 節と併せて使用するとき、GROUP BY フィールドの個別の各値に対して、1 行が検索されます。GROUP BY 節は概念的には Caché の拡張である %FOREACH と似ていますが、%FOREACH はクエリ全体を制約することなく、サブ母集団で集約の選択を許可するのに対し、GROUP BY はクエリ全体で実行します。以下はその例です。

SELECT Home_State, COUNT(Home_State) AS Population
 FROM Sample.Person
  GROUP BY Home_State

このクエリは、各個別の Home_State に対し 1 行を返します。

詳細は、"GROUP BY" 節のリファレンス・ページを参照してください。

HAVING 節

HAVING 節はグループで実行する WHERE 節と同じ働きをします。一般的に、GROUP BY 節または %AFTERHAVING キーワードとの組み合わせで使用します。HAVING 節は、クエリ選択から特定の行を適格とするか、もしくは不適格とします。適格な行とは、condition-expression が True である行です。condition-expression は、AND および OR 論理演算子によってリンク可能な論理テスト (述語) のリストです。condition-expression には集約関数を含めることができます。詳細は、"HAVING" 節のリファレンス・ページを参照してください。

ORDER BY 節

ORDER BY 節は、select-item、あるいは行が表示される順番を指定する項目のコンマ区切りリストが後続する、ORDER BY キーワードで構成されます。各項目は、オプションの ASC (昇順) や DESC (降順) を持ちます。既定は昇順です。ORDER BY 節はクエリの結果に適用し、多くの場合は TOP 節と組み合わせて使用します。詳細は、"ORDER BY" 節のリファレンス・ページを参照してください。

以下の例は、データベースのすべての行に選択されたフィールドを返し、年齢の昇順でこれらの行を並べます。

SELECT Home_State, Name, Age 
FROM Sample.Person
ORDER BY Age

SELECT とトランザクション処理

クエリを実行するトランザクションは、READ COMMITTED または READ UNCOMMITTED のいずれかに定義されます。トランザクション内にないクエリは READ UNCOMMITTED として定義されます。

• READ UNCOMMITTED に定義されている場合、SELECT は現在のデータの状態を返します。それには、コミットされていない進行中のトランザクションによってデータに加えられた変更も含まれます。この変更は、後でロールバックされる場合もあります。

• READ COMMITTED に定義されている場合、SELECT 文の内容によって動作は異なります。通常、READ COMMITTED モードの SELECT 文は、コミットされたデータに挿入と更新の変更のみを返します。削除がコミットされておらず、ロールバックされる可能性がある場合でも、進行中のトランザクションによって削除されたデータ行は返されません。

  しかし、SELECT 文に %NOLOCK キーワード、DISTINCT 節、または GROUP BY 節が含まれる場合、SELECT は、コミットされていない現在のトランザクションの間にデータに加えられた変更を含め、現在のデータ状態を返します。SELECT 内の集約関数でも、指定された列のデータの現在の状態は、コミットされていない変更分を含めて返されます。

詳細は、"SET TRANSACTION" および "START TRANSACTION" を参照してください。

クエリ・メタデータ

ダイナミック SQL を使用すると、クエリ内で指定した列の数、クエリ内で指定した列の名前 (またはエイリアス)、クエリ内で指定した列のデータ型など、クエリに関するメタデータが返されます。詳細は、"Caché SQL の使用法" の "ダイナミック SQL" の章、および "インターシステムズ・クラス・リファレンス" の "%SQL.StatementOpens in a new tab" クラスを参照してください。

例

以下の 4 つの例は、SELECT 節の異なる組み合わせを使用して、同様のクエリを実行します。これらの節は、正しい順序で指定する必要があります。4 つの例ではすべて、Sample.Person テーブルから Name、Home_State、および Age の 3 つのフィールドが選択され、2 つのフィールド (AvgAge と AvgMiddleAge) が計算されています。

HAVING/ORDER BY

以下の例では、AvgAge 計算フィールドは、Sample.Person 内のすべてのレコードで計算されます。HAVING 節は、AvgMiddleAge 計算フィールドを制御し、Sample.Person 内のすべてのレコードから 40 を超えるものの平均年齢を計算します。したがって、すべての行で AvgAge および AvgMiddleAge が同じ値になります。ORDER BY 節は、Home_State フィールド値によって、行の表示をアルファベット順に並べます。

SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 HAVING Age > 40
 ORDER BY Home_State

WHERE/HAVING/ORDER BY

以下の例では、WHERE 節は、指定された北東の 7 つの州に選択肢を限定します。AvgAge 計算フィールドは、これらの Home_States のレコードで計算されます。HAVING 節は、指定された Home_States 内のレコードで 40 を超える平均年齢を計算して、AvgMiddleAge 計算フィールドを設定します。したがって、各行は AvgAge および AvgMiddleAge と同じ値を持ちます。ORDER BY 節は、Home_State フィールド値によって、行の表示をアルファベット順に並べます。

SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 WHERE Home_State IN ('ME','NH','VT','MA','RI','CT','NY')
 HAVING Age > 40
 ORDER BY Home_State

GROUP BY/HAVING/ORDER BY

GROUP BY 節は、AvgAge 計算フィールドが、各 Home_State グループに対して個別に計算されるようにします。また、GROUP BY 節は、各 Home_State で使用される最初のレコードに対する出力表示を制限します。HAVING 節は、各 Home_State グループ内で 40 を超える平均年齢を計算して、AvgMiddleAge 計算フィールドを設定します。ORDER BY 節は、Home_State フィールド値によって、行の表示をアルファベット順に並べます。

SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 GROUP BY Home_State
 HAVING Age > 40
 ORDER BY Home_State

WHERE/GROUP BY/HAVING/ORDER BY

WHERE 節は、指定された北東の 7 つの州に選択肢を限定します。GROUP BY 節は、AvgAge 計算フィールドが、7 つの各 Home_State グループに対して個別に計算されるようにします。また、GROUP BY 節は、指定した各 Home_State で使用される最初のレコードに対する出力表示を制限します。HAVING 節は、7 つの各 Home_State 内で 40 を超える平均年齢を計算して、AvgMiddleAge 計算フィールドを設定します。ORDER BY 節は、Home_State フィールド値によって、行の表示をアルファベット順に並べます。

SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 WHERE Home_State IN ('ME','NH','VT','MA','RI','CT','NY')
 GROUP BY Home_State
 HAVING Age > 40
 ORDER BY Home_State

埋め込み SQL とダイナミック SQL の例

埋め込み SQL とダイナミック SQL は、別の言語のプログラム内から SELECT クエリを発行するために使用できます。埋め込み SQL は ObjectScript コードに組み込むことができます。ダイナミック SQL は ObjectScript コードまたは Caché Basic コードに組み込むことができます。

以下の埋め込み SQL プログラムは、あるレコードからデータの値を取得して、それを INTO 節で指定される出力ホスト変数に代入します。

   NEW SQLCODE,%ROWCOUNT
   &sql(SELECT Home_State,Name,Age
        INTO :a, :b, :c
        FROM Sample.Person)
   IF SQLCODE=0 {
     WRITE !,"  Name=",b
     WRITE !,"  Age=",c
     WRITE !,"  Home State=",a
     WRITE !,"Row count is: ",%ROWCOUNT }
   ELSE {
     WRITE !,"SELECT failed, SQLCODE=",SQLCODE  }

このプログラムで取得される行は (多くても) 1 行であるため、%ROWCOUNT 変数は、0 または 1 に設定されます。複数の行を取得するには、 カーソルを宣言し、FETCH コマンドを使用する必要があります。詳細は、"Caché SQL の使用法" の "埋め込み SQL" の章を参照してください。

以下のダイナミック SQL の例では、まず必要なテーブルが存在するかどうかをテストし、そのテーブルに対する現在のユーザの SELECT 特権をチェックします。次にクエリを実行して結果セットを返します。続いて WHILE ループを使用して、結果セットの最初の 10 レコードに対して %Next メソッドを繰り返し呼び出します。これは、SELECT 文で指定されたようにフィールド位置を指定する %GetData メソッドを使用して 3 つのフィールド値を表示します。

  ZNSPACE "Samples"
  SET tname="Sample.Person"
  IF $SYSTEM.SQL.TableExists(tname)
     & $SYSTEM.SQL.CheckPriv($USERNAME,"1,"_tname,"s")
     {GOTO SpecifyQuery}
  ELSE {WRITE "Table unavailable"  QUIT}
SpecifyQuery
  SET myquery = 3
  SET myquery(1) = "SELECT Home_State,Name,SSN,Age"
  SET myquery(2) = "FROM "_tname
  SET myquery(3) = "ORDER BY Name"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  IF rset.%SQLCODE=0 {
    SET x=0
    WHILE x < 10 {
     SET x=x+1
     SET status=rset.%Next()
     WRITE rset.%GetData(2)," "   /* Name field */
     WRITE rset.%GetData(1)," "   /* Home_State field */
     WRITE rset.%GetData(4),!     /* Age field */
    }
    WRITE !,"End of Data"
    WRITE !,"SQLCODE=",rset.%SQLCODE," Row Count=",rset.%ROWCOUNT
  }
  ELSE {
    WRITE !,"SELECT failed, SQLCODE=",rset.%SQLCODE }

詳細は、"Caché SQL の使用法" の "ダイナミック SQL" の章を参照してください。

関連項目

• SELECT 節 ： DISTINCT、FROM、GROUP BY、HAVING、INTO、ORDER BY、TOP、WHERE

• JOIN、UNION

• CREATE VIEW

• CREATE TABLE、ALTER TABLE、DROP TABLE

• CREATE QUERY、DROP QUERY

• INSERT、INSERT OR UPDATE、UPDATE、DELETE

• "Caché SQL の使用法" の “データベースの問い合わせ” の章

• "Caché 詳細構成設定リファレンス" で説明されている SQL 構成設定

• "Caché エラー・リファレンス" にリストされた SQLCODE エラー・メッセージ