ＳＥのためのＥＸＣＥＬ術

IN句上限を考慮したSQL生成

やりたいこと

＜参照元＞		＜NGパターン＞	＜OKパターン＞
00001 00002 … 01000 01001 … 09999	⇒	SELECT 　* FROM 　SAMPLE_TBL WHERE 　KANRINO IN( 　'00001', 　'00002', 　 … 　'01000', 　'01001', 　 … 　'09999');	SELECT 　* FROM 　SAMPLE_TBL WHERE 　KANRINO IN( 　'00001', 　'00002', 　 … 　'01000') OR KANRINO IN( 　'01001', 　 … 　'09999');

EXCEL関数を使って、別シートなどから値を参照してSQL文を生成する際に
IN句のパラメータは1001件以上になった場合も問題無く実行できるSQLを関数で作成する方法をご紹介。
※ 補足としてORACLE SQL ではIN句のパラメータに1001件以上渡すとエラーとなります。
　 その回避策としてORを条件に入れて再びIN句で1001件目からのSQLを作る方法があります。

ポイントとしては、IN句のパラメータが現在何件目のデータかを把握し、1000件、2000件…9000件の場合だけ OR 演算子でIN句をつなぐSQL文を作成すればよい。 そのためには、ROW関数とMOD関数で何件目かを把握する。

ROW関数

行番号を返す関数です。

・使用例（パラメータを指定している場合）
　ROW(B3) ⇒ 3
　ROW(A5) ⇒ 5

・使用例（パラメータを指定していない場合）
　ROW() ⇒ 関数を記入した行番号

※ ROW() - ROW( WHERE句の１行目のセル ) = 現在のパラメータの件数 がわかりますね。

MOD関数

数値を除数で割ったときの余りを返す

・例
　MOD(5, 1000) ⇒ 5
　MOD(3000, 1000) ⇒ 0
　MOD(6021, 1000) ⇒ 21

※ 結果が2行目のように0であれば 1000の倍数　＝　OR演算子を付与すると考えられますね。

実践

では具体的に参照元の値がA1,A2,… のようにA列に入っている場合、1001件対応の関数を作っていきます。

	A		B
1 2 3 4 5 6 7 8 9	00001 00002 00003 00004 00005 00006 00007 00008 00009		SELECT 　* FROM 　SAMPLE_TBL WHERE 　KANRINO IN ( 　　　… 　〜関数〜 　　　…

まず、前段としてNGパターンのIN句の引数の関数を作ってみます。（下記の関数はB7セルの内容です）

　IF(AND(A1 <> "", A2 <> ""), "'" & A1 & "',", IF(AND(A1 <> "", A2 = ""), "'" & A1 & "');", ""))

はい、複雑ですね〜（笑）

ここで重要なは、参照元(A列) の値が最終行であるかどうかによって閉じ括弧を入れるかなどが変わってきます。
ここでのIF条件は下記の判定をしてます。

AND(A1 <> "", A2 <> "")	⇒	TRUE	⇒	A1は最終行ではない	⇒	'xxxxx',
AND(A1 <> "", A2 = "")	⇒	FALSE	⇒	A1は最終行	⇒	'xxxxx');

ここでさらに1001件以上の考慮をいれると…（ついてきてくださいね 笑）

　IF(AND(A1 <> "", A2 <> "",), IF(MOD(ROW() - ROW($B$6), 1000) = 0,"'" & A1 & "') OR KANRINO IN(" ,"'" & A1 & "',", IF(AND(A1 <> "", A2 = ""), "'" & A1 & "');", ""))

最終行ではない場合にさらに下記の条件を追加してます。

MOD(ROW() - ROW($B$6), 1000) = 0	⇒	TRUE	⇒	1000の倍数件目	⇒	'xxxxx') OR KANRINO IN(
MOD(ROW() - ROW($B$6), 1000) = 0	⇒	FALSE	⇒	1000の倍数件目ではない	⇒	'xxxxx',

以上、になりますが、どうでしょうか？
説明文を読むよりも実際に参考シートをDLしていただいてみたほうがわかるかと思いますので、
詳しくは参考シートで確認ください。

参考シート

IN句のSQL生成シート
⇒サンプルファイルをDL

おすすめ

書籍

一冊あるだけでだいぶちがいますよね！
特にマシン室で作業しているときは、ネット検索できないので強い味方ですね！

ソフト

Officeは仕事の定番ソフト！