SELECT INTOと配列｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2021.05.12

SELECT INTOと配列

Ted Holt 著

また恥をかいてしまいました。私は同僚の前で、RPGプログラムで複数の行を配列データ構造へロードするのに、SELECT INTOステートメントを使用することができると言ったのですが、いやはや、とんだ思い違いをしていました。SELECT INTOとFETCHステートメントを混同していたようです。もちろん、これはカーソルが宣言されているデータを取得します。

しかし、この件はずっと引っ掛かっていました。私は、SELECT INTOのシンプルなところが大好きです。宣言、オープン、フェッチ、およびクローズすべきカーソルはないからです。 SQL PLのFORループが好きであるのと同じ理由ということです。カーソルなしでスカラー変数へ1つのデータ値を取得することができるのに、カーソルなしで配列に2つ以上の値を入れることはできない、というのは理不尽に思えました（今もそう思います）。1つ目の行を1つ目の要素へ、2つ目の行を2つ目の要素へ（以下同様に）入れることの、何がそれほど難しいのでしょうか。「 A caveman could do it. （原始人でもできるくらい簡単です）。」

「成せば成る」ということわざがありますが、「成せば、なまけ者のプログラマーでも成せる」と言えそうです。結論から言うと、SELECT INTOを使用して配列をロードすることは可能なのです。ただ、多少、工夫する必要があるだけです。

説明のために、物理ファイルQCUSTCDTを使用します。これはシステムのQIWSライブラリーにあります。ある通貨単位で100以上の未払金がある顧客のアカウント番号を取得する必要があるとします。単純なSELECTでは、顧客ごとに1つの行が生成されますが、LISTAGG関数を使用して、すべての顧客アカウント番号を1つの長いストリングに結合することができます。アカウント番号はゾーン10進数なので、DIGITS関数で文字データに変換します。

select listagg(digits(c.CusNum))
 within group (order by c.CusNum) as List
   from qcustcdt as c
  where c.BalDue >= 100;

LISTAGGは、行の集合を単一の値に変換します。1つ目のパラメーターは、どのデータを取得するかを指定する式です。2つ目のパラメーターは、取得した値を区切るための値です。ここでは、区切り文字は必要なかったため、2つ目のパラメーターは省略しています。2つ目のパラメーターを指定するとしたら、以下のように、2つの一重引用符を使用して、間に何も入れないようにすれば、区切り文字は入りません。

select listagg(digits(c.CusNum),'')

ソートのやり方が、いつもとは少し異なります。ここでは、LISTAGGでデータのソートを処理できるように、ORDER BYがWITHIN GROUP節に入れられています。この例では、データ構造CustInfoは、メモリーの同じ部分を、スカラー値LISTおよび配列ACCOUNTとして2通りに定義しています。

結果セットは以下のようになります。

192837392859475938583990839283938485

どのようにしてこれを配列に変換するのでしょうか。データ構造を使用します。

**free
dcl-ds CustInfo     qualified;
   List         char(78)             pos(1);
   Account      char( 6)   dim(13)   pos(1);
end-ds CustInfo;

dcl-s  CX               uns( 5);

exec sql
   select listagg(digits(c.cusnum),'')
   within group (order by c.cusnum) as List
     into :CustInfo.List
     from qcustcdt as c
    where c.BalDue >= 100;

CX = 1;
dow CustInfo.Account (CX) <> *blanks;
   dsply CustInfo.Account (CX);
   CX += 1;
enddo;

別の例を示します。顧客がいるすべての州のリストを取得してみましょう。

select listagg(distinct c.State,'')
 within group (order by c.State) as List
   from qcustcdt as c;

この例を示したのは、ある目的のためだけです。すなわち、DISTINCTキーワードを使用すると、返された結果から重複する値を除去できることを示したかったためです。

CACOGAMNNYTXVTWY

結果を固定長の値の配列に入れる必要はありません。たとえば、それらの値を、コンマなどの値で区切りたいという場合もあるかもしれません。その場合は、LISTAGGの2つ目のパラメーターで、区切り文字となるストリングを指定します。以下のステートメントでは、

select listagg(distinct trim(c.City) concat ',' concat trim(c.State),';')
 within group (order by c.City, c.State) as List
   from qcustcdt as c;

以下のストリングが生成されます。

Broton,VT;Casper,WY;Clay,NY;Dallas,TX;Denver,CO;Hector,NY;Helen,GA;Isle,MN;Sutter,CA

この手法は、取得される値の長さが異なる場合（すなわちVARCHAR列またはTRIMされたデータ）に便利です。RPGの%SCAN関数をループ内で使用して、値を抽出することができます。

これまで私が行った作業では、この手法は、1つの列（フィールド）を取得するために使用したことがあっただけでした。この手法は、はたして複数列の結果セットの場合に使えるでしょうか。大丈夫、使えます。以下に、最後の例を示します。

dcl-ds CustInfo     qualified;
   List         char(512)             pos(1);
   Record                   dim(13)   pos(1);
      Account   zoned( 6)             overlay (Record: 1);
      LastName  char ( 8)             overlay (Record: *next);
      City      char ( 6)             overlay (Record: *next);
      State     char ( 2)             overlay (Record: *next);
end-ds CustInfo;

dcl-s  CX               uns( 5);

exec sql
   select listagg(digits(c.CusNum) concat
                         c.LstNam  concat
                         c.City    concat
                         c.State)
   within group (order by c.State, c.LstNam) as List
     into :CustInfo.List
     from qcustcdt as c
    where c.BalDue >= 100;

CX = 1;
dow CustInfo.Record (CX) <> *blanks;
   dsply CustInfo.Record (CX);
   dsply CustInfo.LastName (CX);
   CX += 1;
enddo;

結局のところ、LISTAGGで4つのフィールドの値を連結し、データ構造で4つのフィールドを定義するというだけのことだったようです。

配列への複数行のSELECT INTOについて、お分かりいただけたでしょうか。これは「 It Couldn't Be Done」（できっこないよ）と言われたことです。