Hitachi

ノンストップデータベース　HiRDB Version 10 SQLリファレンス

2.2.1　問合せ式　形式1（一般問合せ式）

〈この項の構成〉

(1)　機能

WITH句中の導出問合せ式と問合せ式本体との組み合わせを指定します。WITH句を用いた場合，導出問合せ式の結果得られる導出表を問合せ名とし，問合せ式本体に指定できます。

問合せ式本体では，問合せ指定，又は問合せ指定の結果得られる導出表同士の和集合，又は差集合を求めるため，集合演算を指定します。和集合を求める場合はUNION〔ALL〕，差集合を求める場合はEXCEPT〔ALL〕を指定します。問合せ式は，カーソル宣言，又は動的SELECT文中のカーソル指定中に指定できます。

ページの先頭へ

(2)　形式

　問合せ式：：＝〔WITH 問合せ名〔（列名〔，列名〕…）〕
　　　　　　　　　　　　　　　　　　AS（導出問合せ式）
　　　　　　　　　　〔，問合せ名〔（列名〔，列名〕…）〕
　　　　　　　　　　　　　　　　　　AS（導出問合せ式）〕…〕
　　　　　　　　問合せ式本体
　
　導出問合せ式：：＝　問合せ式本体
　問合せ式本体：：＝｛問合せ指定
　　　　　　　　　　　｜（問合せ式本体）
　　　　　　　　　　　｜問合せ式本体｛UNION｜EXCEPT｝〔ALL〕
　　　　　　　　　　　　｛問合せ指定｜（問合せ式本体）｝｝

ページの先頭へ

(3)　オペランド

WITH 問合せ名

問合せ式本体の表式に指定する導出表の名称を指定します。WITH句中に複数の問合せ名を指定する場合，同じ問合せ名は指定できません。

列名

WITH句中の一つの導出問合せ式によって指定された導出表から導き出される列に対応させて指定します。

列名についての規則を次に示します。

  1. 列名を省略した場合，次のようになります。

    • WITH句中の導出問合せ式に集合演算を指定しない場合，WITH句中の一つの問合せ指定によって指定された導出表の各列の列名（AS 列名を指定している場合は，AS句で指定した列名）がWITH句問合せ名の列名となります。

    • WITH句中の導出問合せ式に集合演算を指定した場合，導出問合せ式の1番目の問合せ指定によって指定された導出表の各列の列名（AS 列名を指定している場合は，AS句で指定した列名）が，WITH句問合せ名の列名となります。

    ただし，WITH句中の一つの導出問合せ式によって指定された導出表が，列名が同じである二つ以上の列を含む，又は名前のない列を含む場合は省略できません。

  2. 導出表の列が次に示す項目から導き出された列で，AS列名を省略した場合，その列は名前のない列になります。

    • スカラ演算

    • 関数呼出し

    • 集合関数

    • 定数

    • USER値関数

    • CURRENT_DATE値関数

    • CURRENT_TIME値関数

    • CURRENT_TIMESTAMP値関数

    • SQL変数

    • SQLパラメタ

    • コンポネント指定

  3. WITH句中の一つの問合せ名について，同じ名前は指定できません。

  4. WITH句中の一つの問合せ名について，列名で指定する列の数は，そのWITH句の導出問合せ式の結果で得られる導出表の列と同じ数にしてください。

  5. WITH句中の一つの問合せ名について，列名で指定できる列の数の最大値は30,000個です。

表式，問合せ名として導出する列，集合演算，及び行の重複排除の有無を指定します。問合せ式本体の表式に含まれるFROM句には，表名，ビュー表名のどちらかを指定してください。

システム定義pd_sql_modeに「1」を指定した場合は，差集合を求める集合演算子としてEXCEPTに加えてMINUSも指定できます。

WITH句を用いた場合，問合せ式本体の表式に含まれるFROM句には，表名，ビュー表名，又は問合せ名のどれかを指定してください。

WITH句を用いた場合，問合せ式本体には，1行SELECT文は指定できません。

表式，選択する列，及び行の重複排除の有無を指定します。

問合せ指定については，「問合せ指定」を参照してください。

UNION又はEXCEPTによって結合している問合せ指定の組みが二つ以上指定されている場合，その問合せ式本体中の集合演算の評価順序を指定するために問合せ式本体を括弧で囲んで指定します。

二つの問合せ指定，又は問合せ式本体の結果から得られる導出表同士の和集合，又は差集合を求める場合に，指定します。集合演算については，「集合演算の共通規則」を参照してください。

ページの先頭へ

(4)　WITH句中の導出問合せ式についての規則

  1. WITH句中の導出問合せ式によって，問合せ名として導出される列の属性（データ型，データ長，及び非ナル値制約の有無）は，WITH句中の導出問合せ式で指定した導出表の対応する列の属性と同じになります。

  2. WITH句中の導出問合せ式のFROM句には，実表名又はビュー表名を指定してください。問合せ名は指定できません。

  3. WITH句中の導出問合せ式には，選択式に〔表指定.〕ROWは指定できません。

  4. WITH句中の導出問合せ式には，直接含まれるSELECT句に添字付きの繰返し列は指定できません。

  5. WITH句中の導出問合せ式には，次に示す値式は指定できません。

    • XMLコンストラクタ関数

    • SQL/XMLスカラ関数

    • SQL/XML述語

    • SQL/XML集合関数

  6. WITH句中の導出問合せ式のGROUP BY句には，列指定以外の値式は指定できません。

  7. WITH句中の最も外側の問合せ指定の選択式にCASE式を指定した場合，そのCASE式の探索条件には繰返し列を指定できません。

  8. WITH句中の導出問合せ式では，選択式に次の項目を指定できません。

    • WRITE指定

    • GET_JAVA_STORED_ROUTINE_SOURCE指定

    • ウィンドウ関数

ページの先頭へ

(5)　問合せ式本体についての規則

  1. WITH句を用いた場合，問合せ式本体の表式に含まれるFROM句には，表名，ビュー表名，又は問合せ名のどれかを指定してください。

  2. WITH句を用いた場合，問合せ式本体には1行SELECT文は指定できません。

  3. ビュー定義又はWITH句中の導出問合せ式によって導き出される列が，値式を引数とした集合関数の場合，問合せ式本体ではその導出列を外への参照として用いることができません。

    （例）

      WITH QRY1(QC1,QC2) AS (SELECT MAX(C1+C2),AVG(C1+C2) FROM T1),
       QRY2(QC1,QC2) AS (SELECT C1+C2,C3-C4 FROM T2)
    SELECT * FROM QRY1 X
       WHERE QC1>(SELECT QC1 FROM QRY2 WHERE QC2 = X.QC2)

  4. ビュー定義又はWITH句中の導出問合せ式によって導き出される列がCOUNT（＊）又はCOUNT_FLOAT（＊）の場合，問合せ式本体ではその導出列を外への参照として用いることができません。

    （例）

      WITH QRY1(QC1) AS (SELECT COUNT(*) FROM T1),
       QRY2(QC1,QC2) AS (SELECT C1+C2,C3/C4 FROM T2)
   SELECT * FROM QRY1 WHERE EXISTS
       (SELECT * FROM QRY2 WHERE QC1 = QRY1.QC1)

  5. WITH句中の最も外側の問合せ指定の選択式に，次に示す属性の値式を指定して導出した表を問合せ式本体に指定する場合，内部導出表を作成する問合せは指定できません。内部導出表を作成する条件については，「内部導出表」を参照してください。

    • BLOB

    • 最大長が32,001バイト以上のBINARY

    • 抽象データ型

    • 繰返し列

  6. 次に示す属性の関数呼出し，コンポネント指定，SQL変数，及びSQLパラメタによって導出された列を，問合せ式本体の選択式に指定した場合，そのSELECT句を直接含む問合せには，FOR READ ONLYを指定できません。

    • BLOB

    • 定義長が32,001バイト以上のBINARY

    • 抽象データ型

  7. ビュー定義又はWITH句中の導出問合せ式によって導出される列が列指定以外の値式から導出されている場合，その導出列に対してコンポネント指定は指定できません。

  8. ビュー定義又はWITH句中の導出問合せ式によって導出される列がコンポネント指定によって導出されている場合，問合せ式本体ではその導出列を外への参照として用いることができません。

  9. プラグインが提供する関数のうち，受け渡しする値を受信する関数（受渡し値受信関数）と受け渡しする値を送信する関数（受渡し値送信関数）のどちらかを指定して定義したビュー表に対する問合せでは，次のどれかに該当する場合，エラーになります。

    • ビュー定義中のCASE式，又はスカラ関数VALUE中で受渡し値受信関数を指定している。

    • ビュー定義に受渡し値受信関数を指定し，その受渡し値受信関数に対応した受渡し値送信関数を二つ以上指定し，かつ，それらの受渡し値送信関数の第１引数が同じである。

    • ビュー定義に受渡し値受信関数又は受渡し値送信関数のどちらかを指定しかつ，そのビュー表を指定した問合せ指定が内部導出表を作成する。内部導出表を作成する条件については，「内部導出表」を参照してください。

    • ビュー定義に指定した受渡し値受信関数に対応する受渡し値送信関数が，そのビュー定義中と，定義したビュー表を指定した問合せ指定のどれにも存在しない。

    • ビュー表を指定した問合せ指定中の受渡し値受信関数に対応する受渡し値送信関数が，その問合せ指定中と，そのビュー表を定義した問合せのどちらにも存在しない。

ページの先頭へ

(6)　集合演算の共通規則

  1. 集合演算をする場合，その対象になる二つの問合せ指定，導出問合せ式，又は問合せ式本体の結果から得られる導出表をそれぞれ行の集合とみなして，集合演算（和集合，又は差集合を求める）をします。

    したがって，これらの集合演算の対象になる導出表同士の構成列の列数，及び列の並び順は，同じにしてください。また，対応する列のデータ型は，比較できるデータ型にしてください。ただし，日付データと日付データの文字列表現，時刻データと時刻データの文字列表現，時刻印データと時刻印データの文字列表現，日間隔データと日間隔データの10進数表現，及び時間隔データと時間隔データの10進数表現とは集合演算できません。

  2. 集合演算の対象になる導出表同士の列が文字データ型の場合，対応するそれぞれの列の文字集合はすべて同じにしてください。ただし，2番目以降の問合せ指定によって指定された導出表の列が次に示す値式の場合，1番目の問合せ指定によって指定された導出表の列の文字集合に変換されます。

    • 文字列定数

  3. 集合演算で導出される表の列名は，問合せ式本体の1番目の問合せ指定によって指定された導出表の各列の列名（AS 列名を指定している場合は，AS句で指定した列名）となります。

  4. 導出表の列が次の項目から導き出された列で，AS列名を省略した場合，その列は名前のない列になります。

    • 四則演算，日付演算，時刻演算，連結演算，又はシステム組込みスカラ関数を含む値式

    • CASE式

    • CAST指定

    • 集合関数

    • 定数

    • USER値関数

    • CURRENT_DATE値関数

    • CURRENT_TIME値関数

    • CURRENT_TIMESTAMP値関数

    • SQL変数

    • SQLパラメタ

    • コンポネント指定

    • 関数呼出し

  5. 集合演算の結果，得られた導出表の構成列の列数，及び列の並び順は演算対象の導出表の構成列と同じになります。

  6. 定数，集合関数，日付演算，時刻演算，四則演算，CASE式，及びCAST指定の結果は，非ナル値制約なし（ナル値を許します）になります。

  7. 集合演算をする場合，問合せ指定中のFROM句に問合せ式本体は指定できません。

  8. 集合演算をする場合，対象となる導出表の構成列には，結果が次のデータ型となる値式は指定できません。

    • BLOB

    • 最大長が32,001バイト以上のBINARY

    • BOOLEAN

    • 抽象データ型

  9. 集合演算を指定する場合，対象になる導出表の列に，繰返し列を含むことはできません。

  10. 集合演算をする場合，対象となる導出表の列にWRITE指定，GET_JAVA_STORED_ROUTINE_SOURCE指定，及びウィンドウ関数は指定できません。

  11. 集合演算の結果のデータ型，及びデータ長は次のとおりです。

    ＜文字データ，各国文字データ，混在文字データの場合＞

    • 値式のどれかが可変長データの場合の結果のデータは，可変長データになります。

    • 結果のデータ長は，一番長いデータ長を持つ値式のデータ長になります。

    • 値式に文字データと混在文字データが含まれる場合の結果のデータ型は，混在文字データになります。

    ＜数値データの場合＞

    数値データの場合の，結果のデータ型を次に示します。

    オペランド1

    オペランド2

    SMALLINT

    INTEGER

    DECIMAL

    SMALLFLT

    FLOAT

    SMALLINT

    SMALLINT

    INTEGER

    DECIMAL

    SMALLFLT

    FLOAT

    INTEGER

    INTEGER

    INTEGER

    DECIMAL

    FLOAT

    FLOAT

    DECIMAL

    DECIMAL

    DECIMAL

    DECIMAL

    FLOAT

    FLOAT

    SMALLFLT

    SMALLFLT

    FLOAT

    FLOAT

    SMALLFLT

    FLOAT

    FLOAT

    FLOAT

    FLOAT

    FLOAT

    FLOAT

    FLOAT

    結果のデータ型がDECIMALの時の精度と位取りを次に示します。p，sをオペランド1の精度と位取り，p2，s2をオペランド2の精度と位取りにします。

    ・精度＝max（p1−s1，p2−s2）＋max（s1，s2）

    ・位取り＝max（s1，s2）

    pd_sql_dec_op_maxprecオペランドの指定値によって，次のように精度が決まります。

    pd_sql_dec_op_maxprecオペランドの値

    集合演算オペランドの結果列と対応する列の精度

    max（p1−s1，p2−s2）＋max（s1，s2）で求めた精度

    集合演算オペランドの結果列の精度

    29

    すべての列が29けた以下

    29けた以下

    max（p1−s1，p2−s2）＋max（s1，s2）で求めた精度

    30けた以上

    エラー

    30けた以上の列を含む

    38けた以下

    max（p1−s1，p2−s2）＋max（s1，s2）で求めた精度

    39けた以上

    エラー

    38

    任意

    38けた以下

    max（p1−s1，p2−s2）＋max（s1，s2）で求めた精度

    39けた以上

    エラー

    pd_sql_dec_op_maxprecオペランドについては，マニュアル「HiRDB システム定義」を参照してください。

    また，INTEGERはDECIMAL（10，0），SMALLINTはDECIMAL（5，0）として扱われます。ただし，非ナル値制約の有無については，対応する構成列がすべて非ナル値制約ありの場合だけ非ナル値制約ありとして扱います。それ以外の場合は非ナル値制約なしとして扱います。

    ＜時刻印データの場合＞

    時刻印データ同士の集合演算ができます。結果のデータ型も同じです。集合演算項が小数秒精度を含む場合の，結果の小数秒精度は，「Q1 集合演算 Q2」でのQ1の小数秒精度をp1，Q2の小数秒精度をp2としたとき，MAX（p1，p2）となります。

    ＜バイナリデータの場合＞

    結果のデータ長は，一番長いデータ長を持つ値式のデータ長になります。

  12. ALLを指定した場合，重複した行があっても取り除かないでそのまま残し，それぞれの行とします。ALLを省略した場合，重複した行があれば，重複を排除し一つの行とみなします。

    「Q1 集合演算 Q2」の結果の重複行の行数は，ALL指定があるかないかによって異なります。Q1は問合せ式本体（導出問合せ式），Q2は問合せ式本体（導出問合せ式），又は問合せ指定です。Q1中の重複行，Q2中の重複行，Q1及びQ2中の重複行をRとし，重複行RのQ1中の行数をm，Q2中の行数をnとします（m≧0，n≧0）。Q1，Q2，又はQ1とQ2の重複した行がない場合は，重複行Rが1行ある（m＝1，n＝0又はm＝0，n＝1）とみなして，集合演算します。各集合演算の結果の各重複行Rの行数を次に示します。

    集合演算

    ALL指定なし

    ALL指定あり

    UNION

    min（1，n＋m）

    m＋n

    EXCEPT

    1（m＞0 かつ n＝0）

    0（m＝0 又は n＞0）

    max（m−n，0）

ページの先頭へ

(7)　留意事項

  1. 次の条件を満たす場合，HiRDBが作業表を作成することがあります。

    • UNION〔ALL〕，又はEXCEPT〔ALL〕を指定している

    このとき，作業表の行長によっては，上記の処理が制限されることがあります。作業表の行長については，マニュアル「HiRDB システム導入・設計ガイド」を参照してください。

ページの先頭へ

(8)　指定例

<WITH句を用いた場合>

    DECLARE CR1 CURSOR FOR
        WITH QRY1(QSCODE,QSNAME,QCOL,QURIAGE) AS
            (SELECT SCODE,SNAME,COL,TANKA*ZSURYO FROM ZAIKO)
                SELECT QSNAME,MAX(QURIAGE) FROM QRY1 GROUP BY QSNAME

<WITH句を用いない場合>

    DECLARE CR1 CURSOR FOR
        SELECT SCODE,SNAME,COL,TANKA,ZSURYO
            FROM ZAIKO

ページの先頭へ

商品名称に関する表示

All Rights Reserved. Copyright (C) 2018, 2022, Hitachi, Ltd.