SQL对于字符串而言太长 [英] SQL too long for String

问题描述

我要查询以下SQL.这是有效的SQL.不幸的是，对于VBA中的字符串来说太长了.有人知道运行此查询的解决方法吗?

I have the following SQL to be queried. It is a valid SQL. Unfortunately, it is too long for a string in VBA. Anyone knows of a workaround to run this query?

SQL = "SELECT A.cust_ky, A.incid_id, A.OPEN_TS, A.CLOSE_TS, A.REC_UPD_TS, B.wrkgp_id, A.CURR_AGNT_KY, A.incid_ttl_dn " _
    & "FROM (MAINTBLS.INCID_FAB A INNER JOIN MAINTBLS.DEPTMNT B ON A.curr_wrkgp_ky=B.wrkgp_ky) " _
    & "WHERE B.wrkgp_id='" & wrkgp & "' And (A.open_fg = 1 OR A.pend_fg = 1)" _
    & "ORDER BY A.cust_ky, A.curr_agnt_ky ASC"

rs.Open SQL, con, adOpenKeyset

推荐答案

由于使用的是Oracle，因此应使用绑定变量而不是动态SQL，然后在命令对象的参数集合中设置值.它不仅可以防止SQL注入，而且可以更好地优化查询.

Since you use Oracle, you should use a bind variable instead of dynamic SQL and then set the value in the parameter collection of the command object. Not only will it prevent SQL Injection, but it will better optimize your query.

此外，看起来您的SQL语句在order by子句之前缺少空格.那很容易导致您的错误.参见下文-未经测试，但应该可以给您带来启发.

Also, it looks like your SQL Statement is missing a space before the order by clause. That could easily cause your error. See below - untested, but should give you the idea.

SQL = "SELECT A.cust_ky, A.incid_id, A.OPEN_TS, A.CLOSE_TS, A.REC_UPD_TS, B.wrkgp_id, A.CURR_AGNT_KY, A.incid_ttl_dn " _
    & "FROM (MAINTBLS.INCID_FAB A INNER JOIN MAINTBLS.DEPTMNT B ON A.curr_wrkgp_ky=B.wrkgp_ky) " _
    & "WHERE B.wrkgp_id= :wrkgp And (A.open_fg = 1 OR A.pend_fg = 1) " _
    & "ORDER BY A.cust_ky, A.curr_agnt_ky ASC"

   With cmd
     .ActiveConnection = conn
     .CommandText = SQL
     .CommandType = adCmdText
     .Parameters.Append .CreateParameter(, adVarChar, adParamInput, wrkgp)
   End With

这篇关于SQL对于字符串而言太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！