VBA执行SQL查询时提示缺少右括号错误求助

错误原因分析 #

1. 冗余括号引发语法解析错误：Oracle中count(distinct 列名)的写法不需要给列名额外加括号，你写的count(distinct(assg_nbr))和count(distinct(empl_id))里的括号属于多余内容，数据库解析时会误判括号匹配关系，直接触发「缺少右括号」提示。
2. From子句缺失表名：SQL里From后只有注释--table，没有指定实际查询的表，这也是语法错误的核心来源之一。

修正后的SQL代码 #

Select 
    count(distinct assg_nbr) as ASG_CNT
    , sum(sel_qty) as "Total Cases"
    , trunc(assg_end_date) as "assg_end_date"
    , count(distinct empl_id) as SEL_CNT
    , case when to_char(assg_end_date, 'D') < 7 then to_char(assg_end_date + (7 - to_char(assg_end_date, 'D')), 'DD-MON-YY') else to_char(assg_end_date, 'DD-MON-YY') end as "W/E DATE"
    , upper(to_char(assg_end_date, 'dy')) as "Day"
    , concat(to_char(assg_end_date, 'hh24'), '00') as "COM_HR"
    , whse_type
From
    你的实际表名 -- 替换成数据库中真实的表名称
where org_code = '13'
--and resel_assg_flg = 'Y'
and assg_end_date between current_Date - 7*5 and current_Date
and whse_item_nbr <> '0' and whse_item_nbr is not null
group by trunc(assg_end_date)
    , case when to_char(assg_end_date, 'D') < 7 then to_char(assg_end_date + (7 - to_char(assg_end_date, 'D')), 'DD-MON-YY') else to_char(assg_end_date, 'DD-MON-YY') end
    , upper(to_char(assg_end_date, 'dy'))
    , concat(to_char(assg_end_date, 'hh24'), '00')
    , whse_type

VBA中运行的注意事项 #

1. 字符串转义处理：VBA用双引号包裹字符串，所以SQL中的双引号别名要改成两个连续双引号（""），或者直接把别名改用单引号（Oracle支持这种写法）。比如把as "Total Cases"改成as 'Total Cases'，避免VBA解析报错。
2. SQL字符串拼接：VBA中长字符串建议拆分拼接，用& vbCrLf &换行，示例代码如下：

Dim sqlStr As String
sqlStr = "Select " & vbCrLf & _
    "    count(distinct assg_nbr) as ASG_CNT" & vbCrLf & _
    "    , sum(sel_qty) as 'Total Cases'" & vbCrLf & _
    "    , trunc(assg_end_date) as 'assg_end_date'" & vbCrLf & _
    "    , count(distinct empl_id) as SEL_CNT" & vbCrLf & _
    "    , case when to_char(assg_end_date, 'D') < 7 then to_char(assg_end_date + (7 - to_char(assg_end_date, 'D')), 'DD-MON-YY') else to_char(assg_end_date, 'DD-MON-YY') end as 'W/E DATE'" & vbCrLf & _
    "    , upper(to_char(assg_end_date, 'dy')) as 'Day'" & vbCrLf & _
    "    , concat(to_char(assg_end_date, 'hh24'), '00') as 'COM_HR'" & vbCrLf & _
    "    , whse_type" & vbCrLf & _
    "From" & vbCrLf & _
    "    你的实际表名" & vbCrLf & _
    "where org_code = '13'" & vbCrLf & _
    "--and resel_assg_flg = 'Y'" & vbCrLf & _
    "and assg_end_date between current_Date - 7*5 and current_Date" & vbCrLf & _
    "and whse_item_nbr <> '0' and whse_item_nbr is not null" & vbCrLf & _
    "group by trunc(assg_end_date)" & vbCrLf & _
    "    , case when to_char(assg_end_date, 'D') < 7 then to_char(assg_end_date + (7 - to_char(assg_end_date, 'D')), 'DD-MON-YY') else to_char(assg_end_date, 'DD-MON-YY') end" & vbCrLf & _
    "    , upper(to_char(assg_end_date, 'dy'))" & vbCrLf & _
    "    , concat(to_char(assg_end_date, 'hh24'), '00')" & vbCrLf & _
    "    , whse_type"

3. 数据库连接校验：确保VBA已通过ADODB.Connection建立有效数据库连接，用ADODB.Recordset对象执行并获取查询结果。

内容的提问来源于stack exchange，提问作者Odin87