gpt4 book ai didi

sql - 如何解决 Excel 因某些 SELECT 语句而崩溃的问题?

转载 作者:行者123 更新时间:2023-12-04 14:23:01 24 4
gpt4 key购买 nike

这两个语句相似,但第二个语句会导致 Excel 在每次执行时崩溃。唯一的区别是 modelmodel return updated rows (我专门设计了这个最小的示例,以便查询在任何一种情况下都返回完全相同的数据,我的实际 SQL 当然是不同的):

  • select *
    from( select *
    from ( select 1 id, 100 val from dual
    union all
    select 2 id, 200 val from dual )
    model
    dimension by (id)
    measures (val)
    rules ( val[1] = val[cv()]+1 ) )
    where val=101
  • select *
    from( select *
    from ( select 1 id, 100 val from dual
    union all
    select 2 id, 200 val from dual )
    model return updated rows
    dimension by (id)
    measures (val)
    rules ( val[1] = val[cv()]+1 ) )
    where val=101

  • 这是 ADO 中错误的一个孤立示例,还是存在使解析器崩溃的已知 SQL 语句类(我什至不确定为什么 ADO 会解析语句而不是将其传递给数据库)。

    这是崩溃版本的完整 VBA 代码:

    Option Explicit
    Sub Go()

    Dim lConn As ADODB.Connection
    Dim lRecordset As ADODB.Recordset
    'Dim lRecordset
    Dim sSQL As String

    Set lConn = New ADODB.Connection
    Set lRecordset = New ADODB.Recordset
    'Set lRecordset = CreateObject("ADODB.Recordset")

    lConn.Open "Provider=MSDAORA;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=devdb)(PORT=1521)))(CONNECT_DATA=(SID=oracle)(SERVER=DEDICATED)));User Id=csuk;Password=thisisnotmyrealpassword;"

    With lRecordset
    sSQL = "select * " & _
    "from( select * " & _
    " from ( select 1 id, 100 val from dual " & _
    " Union all " & _
    " select 2 id, 200 val from dual ) " & _
    " model return updated rows " & _
    " dimension by(id) " & _
    " measures (val) " & _
    " rules ( val[1] = val[cv()]+1 ) ) " & _
    "where val=101"
    .Open sSQL, lConn
    While Not .EOF
    Sheets(1).Cells(1, 1) = ![Val]
    .MoveNext
    Wend
    .Close
    End With

    Set lRecordset = Nothing
    lConn.Close
    Set lConn = Nothing

    End Sub

    作为对评论的回应,我使用 DAO 尝试了相同的 SQL,但令我困惑的是,我们得到了相同的结果。以下代码使 Excel 崩溃,但删除了 return updated rows只需让它按预期工作即可:

    Option Explicit
    Sub Go()

    Dim lWorkspace As DAO.Workspace
    Dim lDatabase As DAO.Database
    Dim lRecordset As DAO.Recordset

    Dim sSQL As String

    sSQL = "select * " & _
    "from( select * " & _
    " from ( select 1 id, 100 val from dual " & _
    " Union all " & _
    " select 2 id, 200 val from dual ) " & _
    " model return updated rows " & _
    " dimension by(id) " & _
    " measures (val) " & _
    " rules ( val[1] = val[cv()]+1 ) ) " & _
    "where val=101"

    Set lWorkspace = DBEngine.Workspaces(0)
    Set lDatabase = lWorkspace.OpenDatabase("", False, False, "Driver={Microsoft ODBC for Oracle};Server=devdb:1521/oracle;Uid=charts_csuk_uksoft;Pwd=thisisnotmyrealpassword;")
    Set lRecordset = lDatabase.OpenRecordset(sSQL, dbOpenDynaset, dbSQLPassThrough)

    With lRecordset
    While Not .EOF
    Sheets(1).Cells(1, 1) = ![Val]
    .MoveNext
    Wend
    End With

    Set lRecordset = Nothing
    Set lDatabase = Nothing
    Set lWorkspace = Nothing

    End Sub

    最佳答案

    这更像是一种解决方法,而不是解决方案,但它可能是隐藏任何 SQL 语句的选项,这会扰乱 ADO(或 Excel,就此而言),在 VIEW 后面。 .对于动态语句(即在运行时更改 View ),您可以考虑使用带有 EXECUTE IMMEDIATE CREATE OR REPLACE VIEW ... 的过程。在他们中。

    关于sql - 如何解决 Excel 因某些 SELECT 语句而崩溃的问题?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18230725/

    24 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com