17/10/19執筆

Oraleメモ

VBAにて簡単なデータ検索機能を作成していてはまりかけた事象

・機能はA列に製品番号を記入して、
　　ボタンを押すとその在庫がB列に表示されるといったもの

【ボタン】

A列 製品番号	B列 在庫
AAA	0
BBB	0
CCC	0

・内部的には製品番号をSQLのINに指定しADODB.Connection.Executeで発行し、
　　Excelに貼り付けるかなりシンプルなもの。

Dim myArray() As Variant
myArray = sheet1.Range("A2", sheet1.Cells(sheet1.Rows.Count, 1).End(xlUp)).Value

'配列の縦方向と横方向のセル範囲の変換
myArray = WorksheetFunction.Transpose(myArray)

'配列をカンマ区切り文字列に変換
 Dim mySQLIN As String
 mySQLIN = "'" & Join(myArray, "','") & "'"

'～～～～～～省略～～～～～～～
mySQL = "WHERE 製品番号 IN (" & mySQLIN & ") "

◆A列に1000件以上の製品番号を入れてボタンを押すと

エラー「ORA-01795: リストに指定できる式の最大数は1000です。」

初めはADODBの問題かと思ったが、ググるとSQLのINと判明。
ORACLEの場合IN句に1000件までの指定しか出来ないようだ。


★回避策
①INに1000件以上入れる場合 ORで区切る・・・・なんとも不細工な策・・・

WHERE 製品番号 IN ('1','2','3'.......'1000') OR 製品番号 IN ('1001','1002'.......)

②EXISTSを指定
　未確認

③複合条件のIN?
　WHERE (製品番号,1) IN ( ('AAA',1), ('BBB',1), ('CCC',1) )
　でいけるっぽい。というかこれでいける。

'配列をカンマ区切り文字列に変換
Dim mySQLIN As String
mySQLIN = "('" & Join(myArray, "',1),('") & "',1)"

'～～～～～～省略～～～～～～～
mySQL = "WHERE (製品番号,1) IN (" & mySQLIN & ") "