时间戳

首页 > 解决方案 > 将列添加到查询时出现 1004 ODBC 常规错误

excel - 将列添加到查询时出现 1004 ODBC 常规错误

问题描述

有效的原始查询

CSQL2 = CSql1 & vbNewLine & _
          "SELECT CASE WHEN TRIM ( y.fbn ) LIKE '%ABC%' THEN ( bd.build_id ) ELSE TRIM ( y.fbn ) END AS fbn, y.number, y.title, y.owner, y.type, y.open_closed," & vbNewLine & _
          "y.date_approved, y.date_submitted_for_approval, y.expires_after, y.status, y.reg," & vbNewLine & _
          "y.site, y.lines, y.cap, y.committed, y.invoiced, CASE" & vbNewLine & _
          "WHEN y.Lines = 'Electrical' THEN 'Electrical_Equipment'" & vbNewLine & _
          "WHEN y.Lines = 'Mechanical' THEN 'Mechanical_Equipment'" & vbNewLine & _
          "WHEN y.Lines = 'Construction' THEN 'Base'" & vbNewLine & _
          "ELSE y.Lines END category, " & vbNewLine & _
          "CASE WHEN y.Lines = 'Electrical' THEN 'Other' " & vbNewLine & _
          "WHEN y.Lines = 'Mechanical' THEN 'Other' " & vbNewLine & _
          "WHEN y.Lines = 'Construction' THEN 'Base' ELSE y.Lines " & vbNewLine & _
          "END subcategory " & vbNewLine & _
          "FROM s1.line_new y LEFT JOIN (SELECT build_id, fbn FROM bd) bd ON bd.fbn = y.fbn " & vbNewLine & _
          "WHERE Lines <> 'NW' AND ( y.Cap <> 0 OR y.Committed <> 0 OR y.Invoiced <> 0 )" & vbNewLine & _
          "AND y.fbn IN ( SELECT DISTINCT fbn FROM bd )" & vbNewLine & _
          "AND y.number NOT IN (SELECT num FROM s1.att) AND" & vbNewLine & CARFilters

无法刷新列表对象的新查询：

CSql2 = CSql1 & vbNewLine & _
          "SELECT CASE WHEN TRIM ( bd.fbn ) LIKE '%ABC%' THEN ( bd.build_id ) ELSE TRIM ( bd.fbn ) END AS fbn, y.number, y.title, y.owner, y.type, y.open_closed," & vbNewLine & _
          "y.date_approved, y.date_submitted_for_approval, y.expires_after, y.status, y.reg," & vbNewLine & _
          "y.site, y.lines, ROUND(SUM(y.capex) OVER ( PARTITION BY y.fbn, lines ),2) AS cap, y.committed, y.invoiced, CASE" & vbNewLine & _
          "WHEN y.Lines = 'Electrical' THEN 'Electrical_Equipment'" & vbNewLine & _
          "WHEN y.Lines = 'Mechanical' THEN 'Mechanical_Equipment'" & vbNewLine & _
          "WHEN y.Lines = 'Construction' THEN 'Base'" & vbNewLine & _
          "ELSE y.Lines END category, " & vbNewLine & _
          "CASE WHEN y.Lines = 'Electrical' THEN 'Other' " & vbNewLine & _
          "WHEN y.Lines = 'Mechanical' THEN 'Other' " & vbNewLine & _
          "WHEN y.Lines = 'Construction' THEN 'Base' ELSE y.Car_Lines " & vbNewLine & _
          "END subcategory  ,mcl.currency_id mcl_currency ,fx_2.local_currency ,fx_2.conversion_rate 2_fx_rate " & vbNewLine & _
          ",mcl.exchange_rate_effective_date,ROUND(fx.rate,4) conversion_rate " & vbNewLine & _
          ",ROUND(SUM(y.cap*fx.rate) OVER ( PARTITION BY y.fbn, lines ),2) AS cap_local " & vbNewLine & _
          "FROM s1.mcl_carline_new y LEFT JOIN (SELECT build_id, fbn FROM bd) bd ON bd.fbn = y.fbn " & vbNewLine & _
          "LEFT JOIN (SELECT number, exchange_rate_effective_date, currency_id FROM s1.mcl_new) mcl ON y.number = mcl.number " & vbNewLine & _
          "LEFT JOIN s1.fx_rate_2 fx_2 ON fx_2.reg = y.reg " & vbNewLine & _
          "LEFT JOIN s2.prcmt_exchange_rate fx ON fx_2.local_currency = fx.to_currency_id " & vbNewLine & _
          "AND mcl.currency_id = fx.from_currency_id AND mcl.exchange_rate_effective_date = fx.effective_date " & vbNewLine & _
          "WHERE Lines <> 'NW' AND ( y.Cap <> 0 OR y.Committed <> 0 OR y.Invoiced <> 0 ) " & vbNewLine & _
          "AND y.fbn IN ( SELECT DISTINCT fbn FROM bd ) " & vbNewLine & _
          "AND y.number NOT IN (SELECT num FROM s1.att) AND " & vbNewLine & CARFilters

Listobject 刷新部分

With CData.ListObjects.Add(SourceType:=0, Source:=CS, Destination:=CData.Range("$A$1")).QueryTable
        .CommandText = CSql2
        .RefreshStyle = xlInsertDeleteCells
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "CData"
        .Refresh BackgroundQuery:=False
    End With

在此 LO 存在期间，我不得不修改查询，添加了一些列；在添加列之前，此刷新一直有效。现在我得到一个 1004 通用 OBDC，但是当我 Debug.Print在我的 SQL 客户端中查询并按原样运行它时，它可以正常工作。

我怎样才能知道是什么导致了 1004？注意 - CSql1 没有改变。

标签： excelvbaamazon-redshift

解决方案

12 小时的工作时间和赏金后不到一个小时；我发现问题。有人删除了在检索结果所需的表上运行查询的用户的权限。

推荐阅读