36. SQLパッケージのあいまい検索
Query/400 であいまい検索を行った経験のある人は多いだろうが、
SQLバッケージとして ILE-RPG であいまい検索を実現しようとすると壁にぶつかってしまう。
Web化が普及した今日では、Web適用業務のひとつとして、ある文字列だけを入力して、
その文字列を含むレコードだけを抽出するという「あいまい検索」は、実は容易ではないのだ。
あいまい検索の原理は Google や Yahoo のような検索エンジンの機能と良く似ている。
原理的には、SELECT文で LIKE を使えば良いということになるのだが、
SELECT .... LIKE '%文字%' という静的な SELECT 文であれば問題ないが、
'%文字%' の部分をフィールドに置き換えると思うように動作しない。
LIKE に続くフィールドの長さを SQLが判読してしまって、例えば、256桁の SERACH という
名前のフィールドを用意して、SEARCH に左詰で '%文字%'を代入したとしても SQL は
256バイトとして判断してしまって後続のブランク文字も判断されて、結果的にはレコードが
見つからないということになってしまうのである。
そこで、LIKE SUBSTR(:SEARCH, 1, LEN) のようにしても、ある程度は動作するが
たちまち別の場合では、動作しないケースが現れて、プログラマーはまたもや頭を抱えてしまうことになる。
Web化を始める開発者でもあいまい検索をしたいという希望は最近、非常に多い。
ここでは IBM のマニュアルにも載っていない、マル秘テクニックを紹介しよう。
答えを聞いてしまえば簡単であるが、256バイトの SEARCH というフィールドに左詰で '%文字%'を代入した
後で、残りのブランクの桁も、すべて「%」で埋め尽くしてしまえばよいのである。
この方法は EnterpriseServer のユーザーからの質問に対して筆者が探し出した方法であって
確実に動作する手法である。
ここに紹介する例は DSPF を使用したものであるが、実際は Web適用業務として使用されている。
それでは、例として、次のように「商品マスター」の商品名の中に「ビデオ」という文字列が含まれている、
商品レコードを抽出する例を紹介しよう。
【 サンプル 】
0001.00 A* 91/10/14 21:54:59 QUATTRO REL-R03M00 5728-PW1
0002.00 A*-----------------------------------------------*
0003.00 A* サブファイル表示 SFLDSPFM *
0004.00 A*
0005.00 A* 商品マスター照会
0006.00 A*
0007.00 A*-----------------------------------------------*
0008.00 A* 11:25:43 QUATTRO REL-R06M00 5714-UT1
0009.00 A DSPSIZ(24 80 *DS3)
0010.00 A MSGLOC(24)
0011.00 A PRINT
0012.00 A*-----------------------------------------------*
0013.00 A R DSPHEAD
0014.00 A*-----------------------------------------------*
0015.00 A TEXT(' 初期画面 ')
0016.00 A*%%TS SD 19930612 135725 QUATTRO REL-V3R2M0 5738-PW1
0017.00 A CF03(03 ' 終了 ')
0018.00 A SETOF(99)
0019.00 A BLINK
0020.00 A 1 27' 商品マスター照会 '
0021.00 A DSPATR(RI)
0022.00 A COLOR(PNK)
0023.00 A 2 2' 商品名 '
0024.00 A SCHFLD 24O B 2 15TEXT(' 商品名 ')
SOSI A 11 15' 商品名を文字列で検索する
0026.00 A できます。 '
0027.00 A DSPATR(HI)
0028.00 A 23 2'F3= 終了 '
0029.00 A COLOR(BLU)
0030.00 A R SFREC01 SFL
0031.00 A TEXT(' ＳＦＬ明細行 ')
0032.00 A*%%TS SD 19930612 144015 QUATTRO REL-V3R2M0 5738-PW1
0033.00 A*------------------------------------------------*
0034.00 A 20
0035.00 AO 99 SFLNXTCHG
0036.00 A GYO 4A O 8 2TEXT(' № ')
0037.00 A 03 DSPATR(UL)
0038.00 A SHCODE 10A O 8 7TEXT(' 商品コード ')
0039.00 A SHNAME 24O O 8 20TEXT(' 商品名 ')
0040.00 A SHTANK 7Y 0O 8 45TEXT(' 単価 ')
0041.00 A EDTCDE(K)
0042.00 A SHSCOD 4A O 8 56TEXT(' 品種コード ')
0043.00 A R SFCTL01 SFLCTL(SFREC01)
0044.00 A TEXT(' ＳＦＬ制御見出 ')
0045.00 A*%%TS SD 19930612 144015 QUATTRO REL-V2R1M0 5738-PW1
0046.00 A*------------------------------------------------*
0047.00 A OVERLAY
0048.00 A* SFLPAG :1 画面での表示行数
0049.00 A* SFLSIZ :ｻﾌﾞﾌｧｲﾙ 全体の大きさ
0050.00 A SFLSIZ(14)
0051.00 A SFLPAG(13)
0052.00 A 41 SFLDSPCTL
0053.00 A 42 SFLDSP
0054.00 A 43 SFLINZ
0055.00 A SFLRNA
0056.00 A 44 SFLCLR
0057.00 A 45 SFLEND(*MORE)
0058.00 A 46 SFLDLT
0059.00 A ROLLUP(14 ' 次頁 ')
0060.00 A ROLLDOWN(15 ' 前頁 ')
0061.00 A CF03(03 ' 終了 ')
0062.00 A CF04(04 'ﾌﾟﾛﾝﾌﾟﾄ')
0063.00 A CF12(12 ' 前画面 ')
0064.00 A SETOF(99)
0065.00 A BLINK
0066.00 A*( SFLEND(*MORE)... 続く / 終りの使用のために SFLMSG は
0067.00 A* 使用しません。 )
0068.00 A DMYFLD 1A O 1 72
0069.00 A* 61 : IMPOSSIBLE TO ROLLUP
0070.00 A 61 ERRMSGID(EDT0411 QPDA/QEDTM
0071.00 A* 62 : IMPOSSIBLE TO ROLLDOWN
0072.00 A 62 ERRMSGID(EDT0410 QPDA/QEDTM
0073.00 A DSPREC 4S 0H SFLRCDNBR(CURSOR)
0074.00 A TEXT('SFL 表示 ﾚｺｰﾄﾞ № ')
0075.00 A LIN 3S 0H TEXT('ｶｰｿﾙ 位置合せ - 行 ')
0076.00 A POS 3S 0H TEXT('ｶｰｿﾙ 位置合せ - 桁 ')
0077.00 A 1 27' 商品マスター照会 '
0078.00 A DSPATR(RI)
0079.00 A COLOR(PNK)
0080.00 A*( 明細行の桁見出し )
0081.00 A 7 2' № '
0082.00 A DSPATR(HI)
0083.00 A* ｴﾗｰ 標識 :61 - 69
0084.00 A 7 7' 商品コード '
0085.00 A DSPATR(HI)
0086.00 A 7 20' 商品名 '
0087.00 A DSPATR(HI)
0088.00 A 7 45' 単価 '
0089.00 A DSPATR(HI)
0090.00 A 7 56' 品種コード '
0091.00 A DSPATR(HI)
0092.00 A*( 見出しの項目 )
0093.00 A 2 2' 検索 '
0094.00 A SCHFLD 24O O 2 9TEXT(' 商品コード ')
0095.00 A R NOREC
SOSI A TEXT(' ＭＳＧ : レコードな
0097.00 A*%%TS SD 19930612 144015 QUATTRO REL-V2R1M0 5738-PW1
0098.00 A OVERLAY
0099.00 A CF03(03 ' 終了 ')
SOSI A 10 5'( 検索されて見つかったレ *
0101.00 A ない。 )'
0102.00 A DSPATR(HI)
SOSI A 16 5' 処理を続けるために実行キ
0104.00 A なさい。 '
0105.00 A DSPATR(HI)
0106.00 A R DSPEND01
0107.00 A TEXT(' 機能キーの表示 ')
0108.00 A*%%TS SD 19930612 144015 QUATTRO REL-V2R1M0 5738-PW1
0109.00 A*----------------------------------------------------*
0110.00 A OVERLAY
0111.00 A CF03(03 ' 終了 ')
0112.00 A 23 2'F3= 終了 '
0113.00 A COLOR(BLU)
0114.00 A 23 41'F12= 前画面 '
0115.00 A COLOR(BLU)
【 サンプル 】
0001.00 H DFTNAME(SMP007) DATEDIT(*YMD/) 0002.00 F********** 商品マスターあいまい検索 ********************************* 0003.00 FSMP007D CF E WORKSTN 0004.00 F SFILE(SFREC01:RRN1) 0005.00 F INFDS(INFDS) 0006.00 F********************************************************************** 0007.00 D INFDS DS 0008.00 D LINE 370 371B 0 0009.00 D BRRN 376 377B 0 0010.00 D TOPRRN 378 379B 0 0011.00 D*( データ・ベース外部データ構造 ) 0012.00 D FMT001 E DS EXTNAME(SHOHIN) 0013.00 D* 作業変数の定義 0014.00 D RRN1 S 4S 0 INZ 0015.00 D MAXGYO S 4S 0 INZ(13) 0016.00 D AR S 1 DIM(24) 0017.00 C*( 初期画面 ) 0018.00 C*----------------------------------------------------+ 0019.00 C START TAG 0020.00 C EXFMT DSPHEAD 0021.00 C*----------------------------------------------------+ 0022.00 C*( CF03 )- 終了 0023.00 C *IN03 IFEQ *ON 0024.00 C SETON LR 0025.00 C RETURN 0026.00 C END 0027.00 C*( ENTER )- 検索 0028.00 C Z-ADD 1 TOPRRN 0029.00 C EXSR READ 0030.00 C*( サブ・ファイルの表示 ) 0031.00 C*----------------------------------------------------+ 0032.00 C DSPLY TAG 0033.00 C WRITE DSPEND01 0034.00 C SETON 4142 0035.00 C EXFMT SFCTL01 0036.00 C SETOFF 4142 0037.00 C*----------------------------------------------------+ 0038.00 C*( CF03 )- 終了 0039.00 C *IN03 IFEQ *ON 0040.00 C SETON LR 0041.00 C RETURN 0042.00 C END 0043.00 C*( CF12 )- 前画面 0044.00 C *IN12 IFEQ *ON 0045.00 C GOTO START 0046.00 C END 0047.00 C END TAG 0048.00 C****************************************************** 0049.00 C READ BEGSR 0050.00 C****************************************************** 0051.00 C EXSR SFLCLR 0052.00 C* ********************************************************** 0053.00 C* * 検索文字列 SERACH = '% 検索文字 %% ... %' に構成する * 0054.00 C* ********************************************************** 0055.00 C MOVEA SCHFLD AR(2) 0056.00 C 1 DO 24 N 4 0 0057.00 C AR(N) IFEQ ' ' 0058.00 C MOVE '%' AR(N) 0059.00 C END 0060.00 C END 0061.00 C MOVEA AR SEARCH 24 0062.00 C* ****************************** 0063.00 C* * SQL 文のカーソルの前準備 * 0064.00 C* ****************************** 0065.00 C* SELECT 文によってカーソル C1 を用意 0066.00 C/EXEC SQL DECLARE C1 CURSOR FOR 0067.00 C+ SELECT T1.SHCODE, T1.SHNAME, T1.SHTANK, T1.SHSCOD, T1.SHSCOD 0068.00 C+ FROM QTRFIL/SHOHIN T1 0069.00 C+ WHERE T1.SHNAME LIKE :SEARCH 0070.00 C+ ORDER BY T1.SHCODE 0071.00 C/END-EXEC 0072.00 C* カーソルをオープン 0073.00 C/EXEC SQL 0074.00 C+ OPEN C1 0075.00 C/END-EXEC 0076.00 C* *********************************** 0077.00 C* * FETCH によって明細行を取り出す * 0078.00 C* *********************************** 0079.00 C SETOFF 80 0080.00 C/EXEC SQL WHENEVER NOT FOUND GOTO EOF 0081.00 C/END-EXEC 0082.00 C 1 DO MAXGYO RRN1 0083.00 C/EXEC SQL 0084.00 C+ FETCH C1 INTO :SHCODE, :SHNAME, :SHTANK, :SHSCOD, :SHSCOD 0085.00 C/END-EXEC 0086.00 C MOVE RRN1 GYO 0087.00 C N80 Z-ADD 1 DSPREC 0088.00 C SETON 80 0089.00 C*----------------------------------------------------+ 0090.00 C WRITE SFREC01 0091.00 C*----------------------------------------------------+ 0092.00 C END 0093.00 C EOF TAG 0094.00 C* カーソルをクローズ 0095.00 C/EXEC SQL 0096.00 C+ CLOSE C1 0097.00 C/END-EXEC 0098.00 C ENDSR 0099.00 C****************************************************** 0100.00 C SFLCLR BEGSR 0101.00 C****************************************************** 0102.00 C*----------------------------------------------------+ 0103.00 C SETON 44 0104.00 C WRITE SFCTL01 0105.00 C SETOFF 44 0106.00 C*----------------------------------------------------+ 0107.00 C ENDSR
【 解説 】
ILE-RPG は、ソース仕様タイプを SQLRPGLE として編集する。
コンパイルは CRTSQLRPGI コマンドを使って
CRTSQLRPGI OBJ(OBJLIB/SMP007) SRCFILE(PGMRLIB/QRPGLESRC)
のようにして行う。
あいまい検索のテクニックとなるのは
0052.00 C* ********************************************************** 0053.00 C* * 検索文字列 SERACH = '% 検索文字 %% ... %' に構成する * 0054.00 C* ********************************************************** 0055.00 C MOVEA SCHFLD AR(2) 0056.00 C 1 DO 24 N 4 0 0057.00 C AR(N) IFEQ ' ' 0058.00 C MOVE '%' AR(N) 0059.00 C END 0060.00 C END 0061.00 C MOVEA AR SEARCH 24
の部分であり、フィールド SEARCH に
%ビデオ% に続いて残りの部分にも、すべて % を代入してから
0067.00 C+ SELECT T1.SHCODE, T1.SHNAME, T1.SHTANK, T1.SHSCOD, T1.SHSCOD 0068.00 C+ FROM QTRFIL/SHOHIN T1 0069.00 C+ WHERE T1.SHNAME LIKE :SEARCH 0070.00 C+ ORDER BY T1.SHCODE
によって検索している。
