[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では使えないのでしょうか？

あるいは、まったく異なるアプローチなど、何かありましたらご示唆ください。
よろしくお願いします

回答No.5

ushi2015

こんにちは
IIFを使った方が速かったですか。
提示されたデータ量だけだとWHERE分けた方が速かったのですが、ダメですね。
あと、ORDER BY は元々UNION後でした済みません。
for文で回しているのはシートの1行目にフィールド名セットしていだけです。
コメントアウトしておいて下さい。

回答No.4

ushi2015

こんにちは、間違えました。
UNION後にしたのはWHEREじゃなくてSORTです。（ORDER BY）

お礼

すみません、これはどういう意味でしょうか？
元々のコードでも、UNION後にORDER BYをかけていたのですが。

ちなみに、for文で回している
Cells(1, i) = Replace(rs.Fields(i - 1).Name, "'", "")
は何をやっているのでしょうか？

回答No.3

ushi2015

こんにちは、No2です
Oracleは持っていないですし、OraOLEDB.OracleでCSVファイルが扱えるのかも
分からないので、SQL文で・・・
UNIONするデータのフィールドでIIFで演算するのはやめてみて、
気休めでUNION後のWHEREにしてみると、
Sub sqltest_a_2()
　　Dim cn As ADODB.Connection
　　Dim rs As ADODB.Recordset
　　Dim Sql As String
　　Dim i As Long
　　Dim ss As Single

　　Range("A1").CurrentRegion.ClearContents

　　ss = Timer

　　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 明細番号,伝票日付,伝票番号,商品名 AS 摘要,単位,数量,単価,金額 AS 合計,NULL AS 納入先伝票計,0 AS 振込計 FROM uriage.csv WHERE 取引区分>0 "
　　Sql = Sql & " UNION ALL "
　　Sql = Sql & "SELECT 明細番号,伝票日付,伝票番号,商品名,単位,数量,単価,NULL,NULL,金額 FROM uriage.csv WHERE 取引区分=0 "
　　Sql = Sql & " UNION ALL "
　　Sql = Sql & "SELECT 0,伝票日付,伝票番号,取引先名,NULL,NULL,NULL,NULL,NULL,NULL FROM uriage.csv "
　　Sql = Sql & "GROUP BY 取引先名, 伝票日付, 伝票番号 "
　　Sql = Sql & " UNION ALL "
　　Sql = Sql & "SELECT 99,伝票日付,伝票番号,'納入先: ' & 納入先名,NULL,NULL,NULL,NULL,SUM(金額),NULL FROM uriage.csv 売上 "
　　Sql = Sql & "LEFT JOIN nonyu.csv AS 納入先台帳 ON 売上.納入先コード=納入先台帳.納入先コード "
　　Sql = Sql & "WHERE NOT ISNULL(売上.納入先コード) "
　　Sql = Sql & "GROUP BY 取引先名,伝票日付,伝票番号,納入先名 "
　　Sql = Sql & ") ORDER BY 伝票日付,伝票番号,明細番号"

　　Set rs = cn.Execute(Sql)
　　For i = 1 To rs.Fields.Count
　　　　Cells(1, i) = Replace(rs.Fields(i - 1).Name, "'", "")
　　Next
　　Range("A2").CopyFromRecordset rs

　　Debug.Print Timer - ss

End Sub
多少は速くなりますか？

お礼

コメントありがとうございます

rs.RecordCountが10万ほどのデータを対象に試してみたところ、
IIFを使った場合が、6.953125
WHEREを使った場合が、8.3125
となりました。
やはりSELECT文を増やすのは負荷になるような気がします

回答No.2

ushi2015

こんにちは
何故、
VBAによらずSQLのみで処理すること
に拘るのですか？

UNION ALL で遅いのなら、個別にExcelシートに抽出してソートするとか、
VBAでMDBを作成してCSVファイルへのリンクテーブルを作成して予めクエリで
処理して取り込むとか、色々試して処理時間の比較してみてはどうですか？

お礼

コメントありがとうございます

> VBAによらずSQLのみで処理することに拘るのですか？

あえて言えば技術的興味でしょうか
SQL処理のことを知ってまだ日が浅いですが、
手続き言語とは違う記述方法が新鮮で楽しいです
このCSV処理は業務上必要なことではありますが、
それをSQL習得のための課題ととらえ、
敢えてSQLだけでやる方法を模索しています

回答No.1

nan93850673

プロバイダが"MSDAORA"だとWITH区は使えません。"OraOLEDB.Oracle"を使ってください。

お礼

コメントありがとうございます

当方、Oracleの環境は一切所持していないのですが
フリーで入手可能なものはありますか？
また、DBMSでなくCSVファイルに対して使用可能ですか？

ざっと検索してみましたが、それらの情報が見つかりませんでした
よろしければ教えてください