言語要素

NULL の処理

NOT NULL データ制約は、1 つのフィールドが 1 つのデータ値を取得することを必要とします。値ではなく NULL を指定することはできません。この制約は、空文字列値の使用を妨げません。詳細は、"CREATE TABLE" コマンドを参照してください。

SELECT 文の WHERE 節または HAVING 節内の IS NULL 述語は、NULL 値を選択します。空文字列値は選択しません。

IFNULL 関数は、フィールド値を評価し、フィールドが NULL に評価された場合、その 2 番目の引数で指定されている値を返します。空文字列値は、NULL でない値として処理されません。

COALESCE 関数は、指定されたデータから最初の NULL でない値を選択します。空文字列は、NULL でない値として処理されます。

CONCAT 関数または連結演算子 (||) が 1 つの文字列と 1 つの NULL を連結すると、結果は NULL になります。詳細は、以下の例を参照してください。

SELECT {fn CONCAT('fred',NULL)} AS FuncCat,   -- returns <null>
       'fred'||NULL AS OpCat                  -- returns <null>

AVG、COUNT、MAX、MIN、および SUM 集約関数は、処理を実行するときに NULL 値を無視します (すべてのフィールドに NULL 値 を持つレコードは存在できないため、COUNT * はすべての行をカウントします)。SELECT 文の DISTINCT キーワードは、その処理に NULL を含みます。指定したフィールドに NULL 値が存在する場合、DISTINCT は NULL 行を返します。

AVG、COUNT、および MIN 集約関数は、空文字列値の影響を受けます。MIN 関数は、ゼロ値を持つ行がある場合でも、空文字列を最小値と判断します。MAX および SUM 集約関数は、空文字列値の影響を受けません。

式の中の NULL

ほとんどの SQL 関数にオペランドとして NULL を指定すると、NULL が返されます。

NULL をオペランドとして持つ任意の SQL 算術演算は、NULL 値を返します。したがって、7+NULL=NULL となります。これには、二項演算子の加算 (+)、減算 (-)、乗算 (*)、除算 (/)、整数除算 (\)、モジュロ (#)、および単項演算子符号のプラス (+) およびマイナス (-) が含まれます。

算術演算で指定された空文字列は、0 (ゼロ) 値として処理されます。空文字列 (6/'') による除算 (/)、整数除算 (\)、またはモジュロ (#) は、<DIVIDE> エラーになります。

NULL の長さ

SQL 内では、NULL の長さは未定義です (<null> を返します)。しかし、空文字列の長さは、長さゼロとして定義されています。詳細は、以下の例を参照してください。

SELECT LENGTH(NULL) AS NullLen,   -- returns <null>
       LENGTH('') AS EmpStrLen    -- returns 0

この例で示すように、SQL LENGTH 関数は SQL の長さを返します。

以下の例に示すように、ASCII 関数を使用して、長さゼロの SQL 文字列を NULL に変換できます。

SELECT LENGTH(NULL) AS NullLen,                  -- returns <null> 
       LENGTH({fn ASCII('')}) AS AsciiEmpStrLen, -- returns <null>
       LENGTH('') AS EmpStrLen                   -- returns 0

ただし、標準 SQL に対する特定の Caché の拡張は、NULL と空文字列のそれぞれの長さを処理します。$LENGTH 関数は、これらの値の Caché 内部表現を返します。NULL は、長さゼロの定義済みの値として表され、SQL の空文字列は、長さ 0 の文字列として表されます。この機能は、ObjectScript と互換性があります。

SELECT $LENGTH(NULL) AS NullLen,    -- returns 0
$LENGTH('') AS EmpStrLen,           -- returns 0
$LENGTH('a') AS OneCharStrLen,      -- returns 1
$LENGTH(CHAR(0)) AS CharZero        -- returns 0 

これらの値の内部表現が有効な別の場所は、%STRING、%SQLSTRING、および %SQLUPPER 関数内です。これらの関数は、空白スペースを値に追加します。NULL は実際には値を持たないため、それに空白を追加すると、長さが 1 の文字列が作成されます。一方、空文字列は文字値を持つため、それに空白を追加すると、長さが 2 の文字列が作成されます。以下の例を参照してください。

SELECT CHAR_LENGTH(%STRING(NULL)) AS NullLen,  -- returns 1
CHAR_LENGTH(%STRING('')) AS EmpStrLen          -- returns 2

この例では、LENGTH ではなく CHAR_LENGTH が使用されることに注意してください。LENGTH 関数は末尾の空白を削除するため、LENGTH(%STRING(NULL)) は長さ 0 を返します。LENGTH(%STRING('')) は、%STRING が末尾の空白ではなく先頭の空白を付加するため、長さ 2 を返します。

ObjectScript と SQL

SQL の NULL が ObjectScript へ出力されると、ObjectScript の空文字列 ("")、つまり長さゼロの文字列で表されます。

長さゼロの SQL 文字列データが ObjectScript に出力されると、長さ 1 の文字列である $CHAR(0) を含む文字列で表されます。

   &sql(SELECT NULL,''
        INTO :a,:b)
  WRITE !,"NULL length: ",$LENGTH(a)         // returns 0
  WRITE !,"empty string length: ",$LENGTH(b) // returns 1

ObjectScript では、通常、値が存在しないことを空文字列 ("") で示します。この値が埋め込み SQL に渡される場合は、以下の例に示すように、NULL 値として処理されます。

  SET x=""
  SET myquery="SELECT NULL As NoVal,:x As EmpStr"
  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 "NoVal:",rset.%Get("NoVal")," length ",$LENGTH(rset.%Get("NoVal")),!      // length 0
    WRITE "EmpStr:",rset.%Get("EmpStr")," length ",$LENGTH(rset.%Get("EmpStr")),!   // length 0
  }
  WRITE "End of data"

定義されていない入力ホスト変数を指定した場合、埋め込み SQL はその値を NULL として処理します。

NULL または空文字列値を埋め込み SQL から ObjectScript に渡すと、NULL は長さ 0 の文字列に変換され、空文字列は長さ 1 の文字列に変換されます。以下に例を示します。

   &sql(SELECT 
        NULL,
        ''
        INTO :a,:b)
  WRITE !,"The length of NULL is: ",$LENGTH(a)         // length 0
  WRITE !,"The length of empty string is: ",$LENGTH(b) // length 1

以下の例では、空白を追加された SQL 空文字列は、文字列長 2 として渡されます。

   &sql(SELECT %SQLUPPER('')
        INTO :y
        FROM Sample.Person)
  WRITE !,"SQL empty string length: ",$LENGTH(y)

演算子の優先順位

SQL-92 標準は演算子の優先順位に関して不正確です。このことについての前提は SQL 実装間で異なります。

• Caché SQL は、既定では算術演算子の優先順位を規定しません。既定では、Caché SQL には演算子の優先順位はなく、算術式は必ず左から右の順番で実行されます。これは、ObjectScript で使用される規則と同じです。したがって 3+3*5 は 30 になります。演算順位を指定するには、括弧を使用します。したがって 3+(3*5) は 18 に等しくなります。開発者は、注意深く括弧を使用して、意図を明示する必要があります。

• Caché SQL は、算術演算子に関する ANSI の優先順位をサポートするように構成できます。これは、システム全体の構成設定です。ANSI の優先順位を構成した場合、演算子 "*"、"\"、"/"、および "#" は、演算子 "+"、"-"、および "||" よりも優先順位が高くなります。優先順位の高い演算子は、優先順位の低い演算子より先に実行されます。したがって 3+3*5 は 18 になります。この優先順位は、必要に応じて括弧を使用することでオーバーライドできます。したがって (3+3)*5 は 30 になります。

  優先順位の構成には、管理ポータルを使用できます。[一般SQL設定] で、[ANSI の演算子優先順位を適用する] チェック・ボックスにチェックを付けて、[保存] ボタンをクリックします。この SQL オプションの変更内容は、すぐにシステム全体で有効になります。このオプションを変更すると、すべてのクエリ・キャッシュがシステム全体で削除されます。

  優先順位は、$SYSTEM.SQL.SetANSIPrecedence()Opens in a new tab メソッドを使用して構成することもできます。

  SQL の優先順位を変更しても、ObjectScript には影響しません。ObjectScript は常に、厳密に左から右へ算術演算子を実行します。

精度と小数桁数

NUMERIC 結果の 精度 (数値内の最大桁数) は、以下のようになります。

• 加算または減算は、resultprecision=max(scale1, scale2) + max(precision1–scale1, precision2–scale2)+1 というアルゴリズムによって決定されます。計算値 resultprecision が 36 より大きい場合、有効桁数の値は 36 に設定されます。

• 乗算は、resultprecision=min(36, precision1+precision2+1) というアルゴリズムによって決定されます。

• 除算 (value1 / value2) は、resultprecision=min(36, precision1–scale1+scale2+max(6, scale1+precision2+1)) というアルゴリズムによって決定されます。

NUMERIC 結果の スケール (小数部の最大桁数) は、以下のようになります。

• 加算または減算は、resultscale=max(scale1, scale2) というアルゴリズムによって決定されます。

• 乗算は、resultscale=min(17, scale1+scale2) というアルゴリズムによって決定されます。

• 除算 (value1 / value2) は、resultscale=min(17, max(6, scale1+precision2+1)) というアルゴリズムによって決定されます。

データ型、有効桁数、および小数桁数の詳細は、SQL の "データ型" を参照してください。

算術関数と三角関数

Caché SQL は以下の算術関数をサポートします。

Caché SQL は以下の三角関数をサポートします。

単項否定演算子

単項否定論理演算子を使用して、論理的に逆の条件を指定します。以下に例を示します。

SELECT Name,Age FROM Sample.Person
WHERE NOT Age>21
ORDER BY Age

SELECT Name,Age FROM Sample.Person
WHERE NOT Name %STARTSWITH('A')
ORDER BY Name

NOT 演算子を条件の前に配置できます (上記のとおり)。または、NOT を単一文字演算子の直前に配置できます。例えば、NOT<、NOT[ などです。NOT とそれによって否定する単一文字演算子の間には、スペースを入れてはなりません。

AND 演算子および OR 演算子

一連の複数条件で 2 つのオペランド間に AND 論理演算子および OR 論理演算子を使用できます。これらの論理演算子は、キーワードまたは記号で指定できます。

記号演算子とオペランドの間にスペースは必要ありません (ただし読みやすくするためにスペースを入れることをお勧めします)。キーワード演算子の前後にはスペースが必要です。

これらの論理演算子は、単項否定論理演算子と組み合わせることができます。WHERE Age<65 & NOT Age=21 のように使用します。

以下の 2 つの例では、論理演算子を使用して、年齢に基づいた課税をスケジュールします。年齢が 20 ～ 40 歳の人は 3 年ごとに、40 ～ 64 歳の人は 2 年ごとに、そして 65 歳以上の人は毎年課税します。以下の例では結果が同じになります。最初の例はキーワードを使用し、2 番目の例は記号を使用しています。

SELECT Name,Age FROM Sample.Person
WHERE Age>20
      AND Age<40 AND (Age # 3)=0 
      OR Age>=40 AND (Age # 2)=0 
      OR Age>=65
ORDER BY Age

SELECT Name,Age FROM Sample.Person
WHERE Age>20
      & Age<40 & (Age # 3)=0 
      ! Age>=40 & (Age # 2)=0 
      ! Age>=65
ORDER BY Age

括弧を使用すると論理演算子をグループにできます。これにより、グループ・レベルを設定できます。評価は、最下位のグループから最上位のグループの順で行われます。以下の最初の例では、AND 条件は 2 番目の OR 条件にのみ適用されます。MA からすべての年齢の人物を返し、NY から 25 才未満の人物を返します。

SELECT Name,Age,Home_State FROM Sample.Person
WHERE Home_State='MA' OR Home_State='NY' AND Age < 25
ORDER BY Age

グループ条件で括弧を使用すると、異なる結果になります。以下の例は、MA または NY から、年齢が 25 才未満の人物を返します。

SELECT Name,Age,Home_State FROM Sample.Person
WHERE (Home_State='MA' OR Home_State='NY') AND Age < 25
ORDER BY Age

• SQL の実行は、簡易版のロジックを使用します。条件が偽になると、残りの AND 条件はテストされません。条件が真になると、残りの OR 条件はテストされません。

• ただし、SQL は、WHERE 節の実行を最適化するため、同じグループ・レベルでの複数条件の実行順序は予測できず、有効ではありません。

1 行コメント

1 行のコメントを指定するには、行頭に 2 つのハイフンを記述します。コメントは独立した行として記述できるほか、SQL コードと同じ行に記述することもできます。SQL コードの後にコメントを続ける場合は、コードの末尾に空白を 1 つ以上置き、その後にハイフン 2 つのコメント演算子を記述します。コメントには、ハイフン、アスタリスク、スラッシュも含め、あらゆる文字を使用できます。その行の最後までがコメントです。

以下の例では、複数の 1 行コメントが記述されています。

-- This is a simple SQL query
-- containing -- (double hyphen) comments
SELECT TOP 10 Name,Age, -- Two columns selected
Home_State -- A third column
FROM Sample.Person -- Table name
-- Other clauses follow
WHERE Age > 20 AND -- Comment within a clause
Age < 40
ORDER BY Age,  -- Comment within a clause
Home_State
-- End of query

複数行コメント

複数行のコメントを指定するには、/* 開始区切り文字と */ 終了区切り文字を使用します。コメントは、1 行または複数の別々の行として記述することも、SQL コードと同じ行で開始または終了することもできます。少なくとも 1 つの空白を使用して、コメント区切り文字を SQL から区切る必要があります。コメントには、ハイフン、アスタリスク、スラッシュも含め、あらゆる文字を記述できます。ただし、*/ 文字の組み合わせは明らかに例外です。

以下の例では、複数の複数行コメントが記述されています。

/* This is 
   a simple 
   SQL query. */
SELECT TOP 10 Name,Age /* Two fields selected */
FROM Sample.Person  /* Other clauses 
could appear here */ ORDER BY Age
/* End of query */

埋め込み SQL コードをコメントアウトする場合、必ずコメントを &sql 指示文の前または括弧内で始めます。以下の例では、2 つの埋め込み SQL コード・ブロックが正しくコメントアウトされています。

   SET a="default name",b="default age"
   WRITE "(not) Invoking Embedded SQL",!
   /*&sql(SELECT Name INTO :a FROM Sample.Person) */
   WRITE "The name is ",a,!
   WRITE "Invoking Embedded SQL (as a no-op)",!
   &sql(/* SELECT Name INTO :b FROM Sample.Person */)
   WRITE "The age is ",b

コメントとして保持される SQL コード

埋め込み SQL 文は、ルーチンの .INT コード・バージョンのコメントとして保持することができます。そのためには以下のように、構成オプションを設定します。

• $SYSTEM.SQL.SetRetainSQL()Opens in a new tab メソッドを呼び出します。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、[.INT コード内で SQL 文をコメントとして残す] の設定が表示されます。

• 管理ポータルに移動します。[システム管理] から、[構成]、[SQL およびオブジェクトの設定]、[一般SQL設定] (システム, 構成, 一般SQL設定) を選択します。この画面で、[.INT コード内で SQL 文をコメントとして残す] の現在の設定を表示できます。既定は “はい” です。

このオプションを [はい] に設定すると、ルーチンの .INT コード・バージョンで SQL 文をコメントとして維持できます。また、“はい” に設定することで、コメントのテキストの中でその SQL 文によって使用されている非 % 変数をすべてリストにできます。このリストの変数を ObjectScript プロシージャの PUBLIC 変数リストにもリストし、NEW コマンドで再び初期化する必要があります。詳細は、このドキュメントの “埋め込み SQL” の章の "ホスト変数" を参照してください。