CODEWITHSUNDEEP
sqlstringoracle-databasevbaexcel
Kyle
Kyle

Reputation: 947

SQL too long for String

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

Upvotes: 2

Views: 19795

Answers (4)

Skidemon
Skidemon

Reputation: 1

I've used the following steps for a 100+ line query

Use a long Query with Excel VBA

  1. Initially create the query in SSMS
  2. Create a sheet called SQL Query
  3. Copy and paste the query in to the SQL Query sheet and delete all rows that are blank or commented row
  4. Incorporate any variables in to the declare statement

  5. Use the following macro to loop through the SQL Query sheet

    LastRowQ = ThisWorkbook.Sheets("SQL Query").Cells(ThisWorkbook.Sheets("SQL Query").Rows.Count, "B").End(xlUp).Row

Count = 2
Do Until Count > LastRowQ

    SQLQTemp = ThisWorkbook.Sheets("SQL Query").Range("B" & Count)
    SQLQuery = SQLQuery & " " & SQLQTemp

    Count = Count + 1

Loop

SQLDatabaseRS.Open SQLQuery

Upvotes: 0

alberic
alberic

Reputation: 1

If you are using VBA in an Excel sheet, why don't you consider putting the SQL query in a protected cell of your document? You could even put it in an hidden sheet of your document.

Something like: 

Cells(6, 2).Select
sqlString = Cells(6, 2).Value

I did it and it works like a charm in my case.

Upvotes: 0

user158017
user158017

Reputation: 2991

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

Upvotes: 5

steve
steve

Reputation: 6020

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.

Upvotes: 2

Related Questions