OPNQRYFの情報です。皆さんは、OPNQRYFのFORMATとGRPFLDを使っていますか。集計が出来ます。たとえば、或るフィールドをグループ化の集計キーにして、金額や個数ノ合計や、データ件数を取り出し、それを元に、RPGで、割合計算をしたりする場合に便利です。RPGだけだと、予め、全件読み込んで、集計をしないと、「全体からの割合」が出せませんよね。この全体の集計なども、OPNQRYFで手軽に出来るわけです。
論より証拠で、ソースを掲載してしまいます。OPNQRYFのグループ処理でぶつかるエラーメッセージ集も見てね。
物理ファイルOPNQRYF1:集計の対象となるデータファイル
出来れば、ここに、数件のデータを入れてみて下さい。KEYには、重複キーを入れて、ここをキーにAMTを集計しよう、というわけです。
A*------------------------------------*
A R TEST
A*------------------------------------*
A KEY 3S 0
A AMT 7P 0
A*
A K KEY
物理ファイルOPNQRYF2:データ(集計結果用のレコード様式を持つファイル)
A*------------------------------------*
A R TEST2
A*------------------------------------*
A KEY 3S 0
A COUNT 7 0
A AMTTOL 15 0
A AMTMAX 15 0
A AMTAVG 15 0
A*
A K KEY
H 1 Y/ 1
F******************************************************
F* D E F I N E F I L E S *
F******************************************************
FOPNQRYF2IP E K DISK
FQPRINT O F 132 OF PRINTER
I******************************************************
I* D E F I N E I N P U T F I E L D *
I******************************************************
ITEST2 01
O******************************************************
O* O U T P U T M O D U L E *
O******************************************************
OQPRINT D 1 01
O 'KEY:'
O KEY J + 1
O 'COUNT:'
O COUNT J + 1
O 'AMTTOL:'
O AMTTOLJ + 1
O 'AMTMAX:'
O AMTMAXJ + 1
O 'AMTAVG:'
O AMTAVGJ + 1
H 1 Y/ 1
F******************************************************
F* D E F I N E F I L E S *
F******************************************************
FOPNQRYF2IF E K DISK
FQPRINT O F 132 OF PRINTER
C******************************************************
C* M A I N - R O U T I N E
C******************************************************
B001 C *IN90 DOUEQ*ON :
001 C READ OPNQRYF2 90:
B002 C *IN90 IFEQ *OFF :
002 C EXCPT#DTL :
E002 C END :
E001 C END :
C* :
C MOVE *ON *INLR :
C RETRN :
O******************************************************
O* O U T P U T M O D U L E *
O******************************************************
OQPRINT E 1 #DTL
O 'KEY:'
O KEY J + 1
O 'COUNT:'
O COUNT J + 1
O 'AMTTOL:'
O AMTTOLJ + 1
O 'AMTMAX:'
O AMTMAXJ + 1
O 'AMTAVG:'
O AMTAVGJ + 1
OPNQRYFを実行するCLP
PGM
/*グループ合計*/
OVRDBF OPNQRYF2 OPNQRYF1 SHARE(*YES)
OPNQRYF OPNQRYF1 +
FORMAT(OPNQRYF2) +
KEYFLD(KEY) +
GRPFLD(KEY) +
MAPFLD((COUNT '%COUNT') +
(AMTTOL '%SUM(AMT)') +
(AMTMAX '%MAX(AMT)') +
(AMTAVG '%AVG(AMT)'))
CALL OPNQRYFR1 /* RPG CYCLE */
POSDBF OPNQRYF1 *START
CALL OPNQRYFR2 /* READ */
CLOF OPNQRYF1
DLTOVR OPNQRYF2
RETURN
ENDPGM
OPNQRYF1 |
KEY |
AMT |
100 |
1000 |
100 |
2000 |
200 |
500 |
200 |
300 |
OPNQRYFの実行をすると、...
OPNQRYF2 |
KEY |
COUNT |
AMTTOL |
AMTMAX |
AMTAVG |
100 |
2 |
3000 |
2000 |
1500 |
200 |
2 |
800 |
500 |
400 |
解説
これは、ファイルOPNQRYF1を、KEYを集計キーにして、AMTの合計などを出しているものです。このOPNQRYFのGRPFLDを指定する処理は、「グループ処理」と呼ばれます。この後の「総合計のみ」を出すのも、この処理の一種とされています。集計値をRPGなどで取り込むために、仮のファイルOPNQRYF2が必要になります。レイアウトを見ていたいただければ、おわかりですが、必要となるものを、元ファイルOPNQRYF1に無いフィールドを作って、MAPFLDで、そのフィールドに、関数を指定すると、RPGでそのフィールドに、関数結果が入っているわけです。
指定可能な、集計用の関数は以下の通りです。
%COUNT
グループ内のレコードのカウント
%SUM グループ内の該当フィールドの値の和
%AVG
グループ内の該当フィールドの値の算術平均
%MAX グループ内での該当フィールドの最大値
%MIN グループ内での該当フィールドの最小値
%STDDEV
グループ内の該当フィールドの標準偏差
%VAR グループ内の該当フィールドの差異
※MAPFLDはなにも、グループ処理だけ、という訳ではなく、MAPFLDのみでも(上記の%関数を利用しなくても)、別の意味で、利用可能です。たとえば、或フィールドに*10したり、フィールドの連結をしたり、分解したり....をQRYSLTで指定するなんてこともね。...詳しくはマニュアルを見てね。
RPGで参照されるファイル、OPNQRYF2が、OPNQRYF1の結果から取られるように、最初に、OVRDBF OPNQRYF2 OPNQRYF1 SHARE(*YES)をします。これをミスすると、実際のディスク上のファイルOPNQRYF2を参照してしまいます。忘れずに!(CPYFRMQRYFの場合は、OVRDBF SHARE(*YES) は不要です。)
「GRPFLD
パラメーターを指定した場合は、グループが昇順で表示されないことがあります。特定の順序を確実にするためには、KEYFLD
パラメーターを使用してください。」と出ているので、KEYFLDを使用したい場合は、以下に注意して下さい。(必ずしも、KEYFLDは無くてもいいのですが、きれいにキー順にレコードが並んで、到着順にセットして欲しい場合に指定します。
注意としては、集計キーとなるフィールドをKEYFLDに指定した場合は、この集計キーの「行き先(FORMAT内)」が無いと、OPNQRYFはエラーでストップします。この場合、以下の方法を利用します。それから、KEYFLD(*FILE)は、グループ集計処理の時には、指定できませんので、ご注意ください。
パターン1 同じ属性で同じフィールド名でFORMATで指定されるフィールドに含まれている。
FORMAT(OPNQRYF2)で指定したフィールドが、OPNQRYFの対象となるファイル(OPNQRYF1)にも存在する場合(フィールドKEYは、OPNQRYF1、OPNQRYF2に存在していますね)、その値は、自動的にセットされます。但し、集計キー以外の値の指定はどうなるか、私には不明です。つまり、元データに存在する、集計キーに密接に従属する値(たとえば、集計キー=社員番号の場合、それに従属する、社員名など、1対1の関係になるもの)の「以外」の値は、なにが入るのか不明です。(だいたい、この指定は変ですよね。)
パターン2 同じ属性で異なるフィールドに対して、MAPFLDにおいて指定する。
同じフィールド名が無くても、集計キーと全く同じ属性のフィールド(たとえば、OPNQRYF2にKEYWRK(3S 0)というフィールドがある場合)であれば、
MAPFLD((KEYWRK KEY))
として、指定可能です。この場合、集計キーの値は、OPNQRYF2のKEYWRKに入ります。(尚、テストで、OPNQRYF1でゾーン3桁で、OPNQRYF2でパック3桁にして見ましたが、エラーは無く、データも正常でした。でも、やはり、同じ属性(ゾーンならゾーン)にすべきだと思います。)
新規の(OPNQRYF1に無い)フィールドが、FORMATで指定するファイル(OPNQRYF2)に、たくさん有ると、面倒です。というのは、MAPFLDでは、このFOMRATのファイルの中の処理に必要なフィールドを、すべて指定せねばエラーになるからです。従って、必要最小限にしておきましょう。
合計のフィールドは、なるべく大きい桁にしないと、桁あふれで、エラーが出ます。あくまで、FORMATを指定するときは、それを、作業用ファイル、として割り切ってしまった方がいいでしょう。大きな桁にして、それを、RPGで、帳票などのフィールドに転送したほうが良いでしょう。
グループ集計では、総合計は出てきません。注意して下さい。総合計が必要なら、もう一度、OPNQRYFをする必要が有ります。CPYFRMQRYFが便利かもしれません。(下記参照)
DB2 OS/400 用データベース・プログラミング
2.2.3.25 ランダム処理のための QUERY ファイルのオープン
(OPNQRYF) コマンドの使用
これまでの例の大部分は、順次処理を使用する OPNQRYF
コマンドを示しています。ほとんどの場合に、ランダム処理命令(たとえば、RPG/400
言語命令の CHAINまたは COBOL/400 言語命令の READ)を使用することができます。しかし、グループ機能または固有キー機能を使用する場合には、ファイルをランダムに処理することはできません。 |
ここにあるように、ランダムアクセス処理は出来ないと、言っています。実際に実行すると、RPGでエラーを引き起こします(CPF5149)。そこで、RPGで、CHAIN命令など、キー操作をしたい場合は、CPYFRMQRYFで、別に独立したファイルにしてしまいましょう。また、キー操作をしなければ(SETLLもだめ)、サイクルでも、READでも可能です。
グループ集計と総合計の処理の違い(両者ともにグループ処理の一つ)は、OPNQRYFのGRPFLDが、あるか、無いかで、決定されます。総合計処理と、通常の処理の違いは、関数で、%SUMなどが、MAPFLDに有るか否かによります。それから、自分の理解に間違いがなければ、QRYSLTとGRPSLTの違いは、
元データを選択するのがQRYSLT、集計後の値を選択するのがGRPSLT
です。つまり、
元データ(OPNQRYF1) → [QRYSLTで選択] → 集計処理 → [GRPSLTで選択] → OPNQRYF2 → RPG
となります。
PGM
/*総合計*/
OVRDBF OPNQRYF2 OPNQRYF1 SHARE(*YES)
OPNQRYF OPNQRYF1 +
GRPSLT('COUNT *NE 0') +
FORMAT(OPNQRYF2) +
MAPFLD((KEY '999') +
(COUNT '%COUNT') +
(AMTTOL '%SUM(AMT)') +
(AMTMAX '%MAX(AMT)') +
(AMTAVG '%AVG(AMT)'))
CALL OPNQRYFR1 /* RPG CYCLE */
POSDBF OPNQRYF1 *START
CALL OPNQRYFR2 /* READ */
CLOF OPNQRYF1
DLTOVR OPNQRYF2
RETURN
ENDPGM
解説
OPNQRYF1 |
KEY |
AMT |
100 |
1000 |
100 |
2000 |
200 |
500 |
200 |
300 |
OPNQRYFの実行をすると、...
OPNQRYF2 |
KEY |
COUNT |
AMTTOL |
AMTMAX |
AMTAVG |
999 |
4 |
3800 |
2000 |
950 |
基本的に、前述のグループ処理と同じです。違いは、GRPFLDが無いだけです。集計キーが無い集計は、「総合計」となるわけです。後は、上記の「グループ集計」を参考にして下さい。
問題は、元データが無い場合に、オープンした後の処理です。なにもしないと、必ず、RPGでCPF5305(マッピングエラー)が出ます。集計キーを指定したグループ処理では、このエラーは、でません。不思議でした。実はこれに気づいたのは、2000年のテストをしていたときです。QRYSLTで元データを選択してから、総合計を出そうとしているプログラムが、このエラーを出したのです。つまり、データはあるのに、QRYSLTで全部対象外になったのです。早い話、元データをCLRPFMしたのと同じ前提になったわけです。(CLRPFM
OPNQRY1をしてからGRPSLTを消して、実行してみて下さい。)テストで、上記のプログラムを作り、何度も、試行錯誤を繰り返し、マニュアルに当たり、デバッグをして、やっと、分かりはじめました。総合計の場合、何か空白値を持つレコードが、1レコード出来るらしいのです。*NULLという空白値は、私にとって、未だに、妙な存在です。これに気づいたのは、CPYFRMQRYFをすると、元データが0件のはずなのに、合計レコードが、1件(数値は0)で、出来てしまうからです。
そこで、GRPSLTで、集計値の件数が0の場合は、データをよこさない指定をしました。これで、CPF5305は出なくなりました。これから、総合計をやろうとしている、あなた。対象データが無い場合のテストも忘れずに。ILEは、もしかすると、うまくいくかもしれません(知りません)が、OPMの場合、エラーが出ることになるので、必ず、%COUNTを指定して、GRPSLTでそれが0でないとき、と、条件設定しましょう。
それから、上記のCLPで、MAPFLD(KEY '999')としているのは、このサンプルを作るに当たり、同じファイルOPNQRYF2を流用しているために、グループ集計の時には、必要だった、KEYは、総合計には不要となります。OPNQRYF実行時にエラーとなったか、よく覚えていないのですが、KEYに999をセットしました。これだと、固定的に999が入ります。たしか、これを入れないと、実行時にOPNQRYFで、エラーになったような気がします。追記:エラーになります。(CPD3165)
※MAPFLDで、固定的に文字を指定する場合は、MAPFLD((FIELD '"AAAAA"'))です。ダブルコーテーションに注意して下さい。数字をセットする場合は、MAPFLD((FIELD
'9999'))となります。
H 1 Y/ 1
F******************************************************
F* D E F I N E F I L E S *
F******************************************************
FOPNQRYF2IF E K DISK
FQPRINT O F 132 OF PRINTER
C******************************************************
C* M A I N - R O U T I N E
C******************************************************
C Z-ADD999 KEY :
C KEY CHAINOPNQRYF2 90 :
B001 C *IN90 IFEQ *OFF :
001 C EXCPT#DTL :
E001 C END :
C* :
C MOVE *ON *INLR :
C RETRN :
O******************************************************
O* O U T P U T M O D U L E *
O******************************************************
OQPRINT E 1 #DTL
O 'KEY:'
O KEY J + 1
O 'COUNT:'
O COUNT J + 1
O 'AMTTOL:'
O AMTTOLJ + 1
O 'AMTMAX:'
O AMTMAXJ + 1
O 'AMTAVG:'
O AMTAVGJ + 1
PGM
DCL &RTNLIB *CHAR 10
/* CPYFRMQRYFの利用*/
CHKOBJ QTEMP/OPNQRYF2 *FILE
MONMSG CPF9800 *N DO
RTVOBJD OPNQRYF2 *FILE RTNLIB(&RTNLIB)
CRTDUPOBJ OPNQRYF2 &RTNLIB *FILE QTEMP
ENDDO
CLRPFM QTEMP/OPNQRYF2
/*グループ集計*/
OPNQRYF OPNQRYF1 +
FORMAT(OPNQRYF2) +
KEYFLD((KEY)) +
GRPFLD((KEY)) +
MAPFLD((COUNT '%COUNT') +
(AMTTOL '%SUM(AMT)') +
(AMTMAX '%MAX(AMT)') +
(AMTAVG '%AVG(AMT)'))
CPYFRMQRYF OPNQRYF1 QTEMP/OPNQRYF2 +
MBROPT(*ADD) FMTOPT(*NOCHK)
CLOF OPNQRYF1
/*総合計*/
OPNQRYF OPNQRYF1 +
FORMAT(OPNQRYF2) +
GRPSLT('COUNT *NE 0') +
MAPFLD((KEY '999') +
(COUNT '%COUNT') +
(AMTTOL '%SUM(AMT)') +
(AMTMAX '%MAX(AMT)') +
(AMTAVG '%AVG(AMT)'))
CPYFRMQRYF OPNQRYF1 QTEMP/OPNQRYF2 +
MBROPT(*ADD) FMTOPT(*NOCHK)
CLOF OPNQRYF1
/* RPG */
OVRDBF OPNQRYF2 QTEMP/OPNQRYF2
CALL OPNQRYFR1 /* RPG CYCLE */
CALL OPNQRYFR2 /* READ */
CALL OPNQRYFR3 /* CHAIN */
DLTOVR OPNQRYF2
DLTF QTEMP/OPNQRYF2
RETURN
ENDPGM
解説
OPNQRYF1 |
KEY |
AMT |
100 |
1000 |
100 |
2000 |
200 |
500 |
200 |
300 |
OPNQRYFの実行(2つ)をすると、...
OPNQRYF2 |
KEY |
COUNT |
AMTTOL |
AMTMAX |
AMTAVG |
100 |
2 |
3000 |
2000 |
1500 |
200 |
2 |
800 |
500 |
400 |
999 |
4 |
3800 |
2000 |
950 |
CPYFRMQRYFで、作成されるファイルは、FORMATで指定したファイルと同じフィールドを、CRTFILE(*YES)で作成してくれますが、残念ながらキー付きではなく、到着順ファイル(キー無し)になります。合計だけなら、相対レコード番号1でCHAINしても良いのですが、グループ処理でも、集計キー単位に、CHAINしたい場合は、論理ファイルを作るか、作成されたファイルにOPNQRYF
KEYFLD()をもう一度実行するか、上記のようにCRTDUPOBJで、キー付きファイルをQTEMPに作成して、そこへCPYFRMQRYFをします。このとき、注意して欲しいのは、QPYFRMQRYFで作成されるファイルのフィールドは、ALWNULLが指定れています。元ファイルのOPNQRYF2に、このフィールドレベルのキーワードが無ければ、レコードのレベルIDが、異なります。このためでしょうが、CPYFRMQRYFで、FMTOPT(*NOCHK)を要求されます。
データ管理 バージョン 3
4.3 コピー先ファイル作成(CRTFILE パラメーター)
QUERY ファイルからのコピー (CPYFRMQRYF) コマンド上に CRTFILE(*YES)
を指定した場合、新しいコピー先ファイルのファイル・レベルおよび形式レベル識別コードは、その新しいコピー先ファイルが作成されたときに生成されます。ほとんどの場合、新しいコピー先ファイルの形式レベル識別コードは、オープン
QUERY ファイル (OPNQRYF) コマンドの FORMAT
パラメーターに指定されている形式の識別コードと同じになります。OPNQRYF
コマンドによって、新しいコピー先ファイルの形式が変更される場合があります。たとえば、OPNQRYF
コマンドが次のグループ化関数のいずれかを使用している場合は、新しいコピー先ファイルの形式は空白可能になります。
%STDDEV
%VAR
%SUM
%AVG
%MIN
%MAX
形式が変更された新しいコピー先ファイルは、OPNQRYF
コマンドに指定された形式レベル識別コードとは異なる形式レベル識別コードを持ちます。 |
1998/10/15
|