Why can't I do a "with x as (...)" with ADODB and Oracle?

Question

I fail to execute an SQL query with a with clause via ADODB and Oracle.

That is, the following snippet works:

Dim cn As ADODB.connection
Set cn = ....

Dim rs As ADODB.recordSet
Set rs = New ADODB.Recordset

rs.Open "select 'foo' x from dual", cn


Do While Not rs.eof
   ...
   rs.MoveNext
Loop

However, the following doesn't work - it genererats a Run-Time error 3704: Operation is not allowed when the object is closed.

Dim cn As ADODB.connection
Set cn = ....

Dim rs As ADODB.recordSet
Set rs = New ADODB.Recordset

rs.Open "with w as (select 'foo' x from dual) select x from w", cn

Do While Not rs.eof
   ...
   rs.MoveNext
Loop

Obviously, this is a trimmed-down demonstration of the real problem which consists of a more sophisticated query.

It seems to me that ADODB sort of parses the query before it passes it to the Oracle instance and does not understand the with clause. Anyway, any help on this is highly appreciated.

Deliberation

Answer 1

Ok, it really seems as though ADODB expects a query statement to actually start with select. Therefore, a work around for the problem might be to enclose the statement in a select * from ( .... ) like so:

Dim sql As String
sql = "with w as (select 'foo' x from dual) select x from w"

' enclose the statement:
sql = "select * from (" & sql & ")"

rs.Open sql, cn

Deliberation

Ohhhhhhhhhhhhhhh man that's gonna save me a lot of work! I write a lot of Oracle queries in ADODB and have been avoiding using the WITH satement. This will provide much more clarity. I would up vote this a hundred times if I could. – Nino

I don't know what to say. This is soooo stupid on MS's part. a serious WTF. – Elam

That saved me some time! Thank you. – Goofball

Thanks for this answer! – Amorist

OMG Thanks! It has been 5 days that I am looking for a solution to this problem! – Supplemental

Answer 2

Above method did not work for me.

Adding ";" prior to WITH keyword resolved the issue.

Dim sql As String sql = ";with w as (select 'foo' x from dual) select x from w"

rs.Open sql, cn

Chihli

Works as advertised. – Mayda