为什么我不能使用"x as(...)"来做“与ADODB和Oracle? [英] Why can't I do a "with x as (...)" with ADODB and Oracle?

问题描述

我无法通过ADODB和Oracle使用 with子句执行SQL查询.

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

但是，以下操作无效-产生运行时错误3704:关闭对象后，不允许进行操作.

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.

在我看来，ADODB在将查询传递给Oracle实例之前先对其进行了解析，并且不理解 with子句.无论如何，这方面的帮助很大 赞赏.

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.

推荐答案

好吧，ADODB似乎确实希望查询语句实际上以select开头. 因此，解决该问题的方法可能是将该语句括在select * from ( .... )中，如下所示:

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

这篇关于为什么我不能使用"x as(...)"来做“与ADODB和Oracle?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！