[Excel ADO] WITH句の可否
Excel2007のADOを使ってCSVファイルのデータを集計する方法を習得中です
http://okwave.jp/qa/q9081610.htmlで質問させていただきました内容で、
uriage.csv
伝票日付,伝票番号,取引先名,明細番号,納入先コード,商品名,単位,数量,単価,金額,取引区分
270901,00000664,X社,1,0071,商品A,個,4,2250,9000,1
270901,00000664,X社,2,0071,商品B,個,4,1750,7000,1
270901,00000664,X社,3,0071,消費税,,0,0,1280,2
270903,00000739,X社,1,0067,商品C,式,1,0,0,1
270903,00000739,X社,2,0067,商品D,個,1,0,0,1
270915,00000360,X社,1,,振込,,0,0,4320,0
270915,00000363,X社,1,,振込,,0,0,78840,0
270915,00000363,X社,2,,振込,,0,0,135540,0
271022,00000831,Y社,1,0090,商品B,式,1,10000,10000,1
271022,00000831,Y社,2,0090,消費税,,0,0,800,2
271022,00000832,Y社,1,0144,商品D,個,1,2000,2000,1
271022,00000832,Y社,2,0144,消費税,,0,0,160,2
271105,00000447,Y社,1,,振込,,0,0,2160,0
271110,00000459,Y社,1,,振込,,0,0,10800,0
nonyu.csv
納入先コード,納入先名
0067,本社
0071,M支店
0084,N支店
0090,本社
0109,S支店
0144,T支店
というテーブルに対して、以下のようなSQLを発行することで、VBAによらずSQLのみで処理することができるようになりました（実際はもう少し複雑なSQLですが、質問に際して簡略化しました）
Sub sqltest()
Dim cn As ADODB.Connection
Dim rs As Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.Path & ";" & _
"Extended Properties='Text;HDR=YES'"
cn.CursorLocation = adUseClient
Sql = "SELECT * FROM ( "
Sql = Sql & "SELECT 明細番号,伝票日付,伝票番号,商品名,単位,数量,単価,IIF(取引区分>0,金額),NULL,IIF(取引区分=0,金額) FROM uriage.csv 売上"
Sql = Sql & " UNION ALL " & _
"SELECT 0,伝票日付,伝票番号,取引先名,NULL,NULL,NULL,NULL,NULL,NULL FROM uriage.csv 売上" & _
" GROUP BY 取引先名,伝票日付,伝票番号"
Sql = Sql & " UNION ALL " & _
"SELECT 99,伝票日付,伝票番号,'納入先: ' & 納入先名,NULL,NULL,NULL,NULL,SUM(金額),NULL FROM uriage.csv 売上" & _
" LEFT JOIN nonyu.csv AS 納入先台帳 ON 売上.納入先コード=納入先台帳.納入先コード" & _
" WHERE NOT ISNULL(売上.納入先コード) " & _
" GROUP BY 取引先名,伝票日付,伝票番号,納入先名"
Sql = Sql & ") ORDER BY 伝票日付,伝票番号,明細番号"
Set rs = cn.Execute(Sql)
Range("A2").CopyFromRecordset rs
End Sub
一応できたことはできたのですが、データが大量になってくるとSELECT文を3回発行しているためかレスポンスが非常に悪くなってしまっています（上記では、全データを対象にしていますが、実際はWHEREにより日付、取引先名、納入先名などで絞り込んでいます。これを3回やるのは目に見えて無駄とは思うのです）
高速化の方策をいろいろ検索してみましたところ、UNIONで繋げるのは愚策で、共通するSQL文をWITH句でまとめるのが良いという記事がありました
しかし、WITHを使おうとしてもエラーとなってしまいます。Excel ADOでは使えないのでしょうか？
あるいは、まったく異なるアプローチなど、何かありましたらご示唆ください。
よろしくお願いします
お礼
CASE文はうまくいかなかったのですが、 ORDER BY IIF(行番号=0,999999,行番号） とすることで、無事解決しました 本当にありがとうございました！