VBA/Excel : Not accepting SQL subclauses

Question

我有一个通过 ADODB 连接到 Oracle 数据库的 Excel/VBA 电子表格。连接工作正常，普通查询运行良好。但我试图变得聪明一点，避免通过使用 with as 子句来运行多个 select 语句。此链接基本上解释了我想要做什么:

https://dba.stackexchange.com/questions/6368/how-to-select-the-first-row-of-each-group

My SQL 在没有 Excel 的情况下直接运行时工作正常。但是，Excel/VBA 的 With As 子句存在问题，并引发“运行时错误 '3704'；应用程序定义或对象定义”错误。这是我的 SQL 和代码的简化版本:

SQL

with ORDERED as (select
start_value, country
from
MYTABLE
where country = '840') select * from ORDERED

VBA代码

Dim dbaRecordSet As ADODB.Recordset
Dim gloDatabase As ADODB.Connection
dim sSQL as string

sSQL = "with ORDERED as (select start_value, country from MYTABLE where      country = '840') select * from ORDERED"
Set gloDatabase = New ADODB.Connection
gloDatabase.ConnectionString = My_Connection_String
gloDatabase.Open
gloDatabase.CursorLocation = adUseClient  

Set dbaRecordSet = New ADODB.Recordset
dbaRecordSet.ActiveConnection = DBConnection
dbaRecordSet.CursorLocation = adUseClient
dbaRecordSet.Source = sSQL
dbaRecordSet.Open

有谁知道为什么 Excel/VBA 拒绝接受 With As () 子句？如果我删除该子句并将其作为普通的 select 语句运行，则一切正常。谢谢建议。

Answer 1

您必须在 VBA 中以稍微不同的方式编写嵌套选择语句。尝试一下

select * from (
    select
        start_value, country
    from
        MYTABLE
    where country = '840'
) ORDERED