UNNEST 表関数
UNNEST 関数は、指定された配列の各エレメントにつき 1 行が含まれる結果表を戻します。 複数の通常配列引数が指定されている場合、行の数はカーディナリティーが最大の配列と一致します。
スキーマは SYSIBM です。
- 配列式
- 配列データ・タイプを戻す式。 式は、以下の式のいずれかでなければなりません。
- SQL 変数
- SQL パラメーター
- グローバル変数
- 関数呼び出し
- パラメーター・マーカーの CAST 指定
- CASE 式
UNNEST 関数によって生成される結果列の名前は、collection-derived-table 節の correlation-clause の一部として提供できます。
UNNEST 関数を使用できるのは、配列がサポートされているコンテキスト内の collection-derived-table 節だけです (SQLSTATE 42887)。
結果表は入力引数に応じて異なります。
- 通常配列を返す単一の array-expression が指定されている場合:
- 配列エレメントが行データ・タイプでない場合、結果は列のデータ・タイプが配列エレメントのデータ・タイプと一致する単一列の表になります。
- 配列エレメントが行データ・タイプの場合、結果はエレメント・データ・タイプの行フィールドごとに 1 列ずつある表になります。 結果表の列のデータ・タイプは、対応する配列エレメント行フィールドのデータ・タイプと一致します。
- 複数の通常配列引数が指定され、どの配列エレメントにも行データ・タイプがない場合、最初の配列は結果表に最初の列を提供し、2 番目の配列は 2 番目の列を提供します。以下同様です。 各列のデータ・タイプは、対応する配列引数の配列エレメントのデータ・タイプと一致します。 配列のカーディナリティーが同一でない場合、結果として生成される表のカーディナリティーは、最大のカーディナリティーを持つ配列と同じになります。 行の配列指標の値が対応する配列のカーディナリティーより大きい場合は常に、表の列値は NULL 値に設定されます。 つまり、各配列が 2 つの列 (1 つは配列指標用、もう 1 つはデータ用) を持つ表として表示される場合、UNNEST は配列の間で、配列指標に対する結合述部として等価を使用して OUTER JOIN を実行します。
- 連想配列を返す単一の array-expression が指定されている場合:
- 配列エレメントが行データ・タイプでない場合、結果は 2 列の表になり、最初の列のデータ・タイプは配列指標のデータ・タイプと一致し、2 番目の列のデータ・タイプは配列エレメントのデータ・タイプと一致します。
- 配列エレメントが行データ・タイプの場合、結果は行データ・タイプのフィールドの数より 1 列多い表になり、最初の列のデータ・タイプは配列指標のデータ・タイプと一致し、残りの列のデータ・タイプは配列エレメント行フィールドのデータ・タイプと一致します。
- 以下の場合にはエラー (SQLSTATE 42884) になります。
- 複数の連想配列引数が指定されている場合。
- 複数の配列引数が指定され、少なくとも 1 つの配列に行タイプのエレメント・データ・タイプがある場合。
- 通常配列引数と連想配列引数が両方とも指定されている場合。
この特別な表関数を使用するのは、FROM 節の table-reference の collection-derived-table においてのみです。
複数の配列があり、少なくとも 1 つの引数が連想配列の場合、エラーが戻されます (SQLSTATE 42884)。
連想配列をネスト解除する際に、WITH ORDINALITY 節が使用されると、エラーが戻されます (SQLSTATE 428HT)。
例
- 配列タイプ
PHONENUMBERS
の通常配列変数RECENT_CALLS
に 3 つのエレメント値 9055553907、4165554213、および 4085553678 のみ含まれていると想定します。 以下の照会を実行します。
以下のようにフォーマット設定された表が戻ります。SELECT T.ID, T.NUM FROM UNNEST(RECENT_CALLS) WITH ORDINALITY AS T(NUM, ID)
ID NUM ---------- 1 9055553907 2 4165554213 3 4085553678
- 配列タイプ
PERSONAL_PHONENUMBERS
の配列変数PHONELIST
から個人の電話番号のリストが返され、索引ストリング値も返されます。以下の照会を実行します。
以下のようにフォーマット設定された表が戻ります。SELECT T.ID, T.PHONE FROM UNNEST(PHONELIST) AS T(ID, PHONE)
ID PHONE ---------------- Home 4163053745 Work 4163053746 Mom 4164789683