SQL too long for String
17,277
Solution 1
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.
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
Solution 2
Create a view for the query, something like this
create view fix_for_broken_vba as
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 (A.open_fg = 1 OR A.pend_fg = 1)
and then rewrite the query accordingly.
Author by
Kyle
Updated on July 12, 2022Comments
-
Kyle almost 2 years
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
-
Kyle about 12 yearsDon't have enough privileges for Create View :( But Thanks tho. I'm know that would work