Why can't I do a "with x as (...)" with ADODB and Oracle?
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 Stringsql = "with w as (select 'foo' x from dual) select x from w"' enclose the statement:sql = "select * from (" & sql & ")"rs.Open sql, cn
Above method did not work for me.
Adding ";" prior to WITH keyword resolved the issue.
Dim sql As Stringsql = ";with w as (select 'foo' x from dual) select x from w"
rs.Open sql, cn