DevOps, CI/CD and Automation

Hello,

I am trying to run the long SQL query at the bottom of this post from an Excel workbook. I am connected via a DNS ODBC connection to Oracle and am using ADO in the VBA code below to get my Recordset.
I have a macro constructed as below to create a string from SQL stored in a worksheet in Excel (housed over nearly 300 rows) and then plug that into my Recordset Open,in order to bring back the dataset that I want.
The macro works when I use a simpler/shorter set of SQL (included below by way of example), and the complex SQL at the bottom works fine when I run it in OraDev, so both the macro and the SQL seem fine on their own.
I keep getting the error messages/codes in the subject though when I try and put the long SQL query through the macro. I've been googling all day to try and figure it out but I need help as I don't understand what the problem is and can not find the answer :(

Have I exceeded a limit or something? Is having such a long string creating invalid characters somehow?

Very stuck, very grateful for any help.

Thanks a million

Jon





VBA code:

Option Explicit

Public Sub GetData()

On Error GoTo ErrHandler:

Dim myFile, fnum

Dim conn As New ADODB.Connection
Dim connString As String
Dim sqlString As String
Dim iCols As Long
Dim rsRecords As New ADODB.Recordset

connString = "DSN=xxx;Uid=xxx;Pwd=xxx"

'sqlString = Join(Application.Transpose(Sheet2.Range("Test")))
sqlString = GenerateSQLString

myFile = "C:\Documents and Settings\xxxx\Desktop\" & "ImmediateWindow.txt"
fnum = FreeFile()
Open myFile For Output As fnum
Print #fnum, sqlString
Close #fnum

conn.Open connString
rsRecords.CursorLocation = adUseServer
rsRecords.Open sqlString, conn, adOpenForwardOnly, adLockReadOnly

If conn.State = adStateOpen Then
Worksheets("Data").Range("A2").CopyFromRecordset rsRecords

For iCols = 0 To rsRecords.Fields.Count - 1
Worksheets("Data").Range("A1").Cells(1, iCols + 1).Value = rsRecords.Fields(iCols).Name
Next
Else
MsgBox "no connection"
End If

rsRecords.Close
Set rsRecords = Nothing
conn.Close
Set conn = Nothing

ErrHandler:
If conn.Errors.Count > 0 Then
MsgBox conn.Errors(0).Description
End If

End Sub


Private Function GenerateSQLString() As String

Dim l As Long
Dim rng As Range
Dim str As String

Set rng = Sheet2.Range("Test")

For l = 1 To rng.Cells.Count
If Len(rng.Cells(l).Value) > 0 Then
str = str & rng.Cells(l).Value & Chr(10)
Debug.Print str
End If
Next l

Debug.Print str

GenerateSQLString = str

End Function

SIMPLE SQL:

SELECT D.DEAL_ID
FROM
GLOBAL_SF.DEAL D
WHERE
D.DEAL_ID = '87417577'
;


MORE COMPLEX SQL:

SELECT
DEAL_ID DEAL_ID
,TEMPTABLE.COMMITTEE_ID COMMITTEE_ID
,TEMPTABLE.COMMITTEE_DT COMMITTEE_DT
,TEMPTABLE.COMMITTEETYPE COMMITTEETYPE
,TEMPTABLE.COMMITTEEPURPOSE COMMITTEEPURPOSE
,TEMPTABLE.COUNTRYNAME COUNTRYNAME
,TEMPTABLE.BUSINESSLINE BUSINESSLINE
,TEMPTABLE.DEAL DEAL
,CASE WHEN UPPER(TEMPTABLE.OOSCREENERCMMTT) LIKE '%RED%' THEN 'RED'
WHEN UPPER(TEMPTABLE.OOSCREENERCMMTT) LIKE '%AMBER%' THEN 'AMBER'
WHEN UPPER(TEMPTABLE.OOSCREENERCMMTT) LIKE '%GREEN%' THEN 'GREEN'
ELSE NULL END DESCISION
,TEMPTABLE.OOSCREENERCMMTT FULLOUTCOME
,TEMPTABLE.PRIMARYANALYST PRIMARYANALYST
,TEMPTABLE.CHAIRVOT CHAIRVOT
,TEMPTABLE.CHAIRNVOT CHAIRNVOT
,TEMPTABLE.CHAIRIND CHAIRIND
,TEMPTABLE.INDPERSON INDPERSON
,TEMPTABLE.VOTER VOTER
,TEMPTABLE.NONVOTER NONVOTER
,TEMPTABLE.SECANALYST SECANALYST
,TEMPTABLE.OBSERVER OBSERVER
,TEMPTABLE.SCREENERCMMTT_COMMENTS SCREENERCMMTT_COMMENTS
,TEMPTABLE.SRVC_ID SRVC_ID
,TEMPTABLE.GEO_SEGMENT GEO_SEGMENT
,TEMPTABLE.NBRPRIMARYANALYST NBRPRIMARYANALYST
,TEMPTABLE.NBRCHAIRVOT NBRCHAIRVOT
,TEMPTABLE.NBRCHAIRNVOT NBRCHAIRNVOT
,TEMPTABLE.NBRCHAIRIND NBRCHAIRIND
,TEMPTABLE.NBRINDEPENDENT NBRINDEPENDENT
,TEMPTABLE.NBRVOTER NBRVOTER
,TEMPTABLE.NBRNONVOTER NBRNONVOTER
,TEMPTABLE.NBRSECONDARYANALYST NBRSECONDARYANALYST
,TEMPTABLE.NBROBSERVER NBROBSERVER
FROM
(SELECT
DEAL.DEAL_ID DEAL_ID,
DEAL.DEAL_LEGAL_NAME DEAL,
BG.BUS_GRP_TYP BUSINESSLINE,
DC.SRVC_ID SRVC_ID,
DC.COMMITTEE_ID COMMITTEE_ID,
DC.COMMITTEE_DT COMMITTEE_DT,
CT.COMMITTEE_TYPE_DESC COMMITTEE_DATE,
SCO.SCRNR_CMTE_OUTCOME_NAME OOSCREENERCMMTT,
DC.SCRNR_COMMENTARY SCREENERCMMTT_COMMENTS,
PRIMAN.PRIMARYANALYST PRIMARYANALYST
,CHAIRVOT.CHAIRVOT CHAIRVOT
,CHAIRIND.CHAIRIND CHAIRIND
,VOTER.VOTER VOTER
,INDPERSON INDPERSON
,NONVOTER.NONVOTER NONVOTER
,SECANALYST.SECANALYST SECANALYST
,OBSERVER.OBSERVER OBSERVER
,CHAIRNVOT.CHAIRNVOT CHAIRNVOT
,CT.COMMITTEE_TYPE_DESC COMMITTEETYPE
,CNTRY.CNTRY_NM COUNTRYNAME
,CMTTPURPOSE.CMTT_PURPOSE_DESC COMMITTEEPURPOSE
,GS.GEO_SEGMENT_NAME GEO_SEGMENT
,NVL(QTITLECNT.CHAIRVOTTOTAL, 0) NBRCHAIRVOT
,NVL(QTITLECNT.INDPTOTAL, 0) NBRINDEPENDENT
,NVL(QTITLECNT.VOTERTOTAL, 0) NBRVOTER
,NVL(QTITLECNT.NONVOTERTOTAL, 0) NBRNONVOTER
,NVL(QTITLECNT.PRIMANLYTOTAL, 0) NBRPRIMARYANALYST
,NVL(QTITLECNT.SECANLYTOTAL, 0) NBRSECONDARYANALYST
,NVL(QTITLECNT.CHAIRINDTOTAL, 0) NBRCHAIRIND
,NVL(QTITLECNT.OBSERVERTOTAL, 0) NBROBSERVER
,NVL(QTITLECNT.CHAIRNVOTTOTAL, 0) NBRCHAIRNVOT
FROM GLOBAL_SF.DEAL_COMMITTEE DC,
FII_CORE.CNTRY CNTRY,
GLOBAL_SF.COMMITTEE_TYPE CT,
GLOBAL_SF.DEAL DEAL ,
FII_CORE.BUS_GRP BG,
GLOBAL_SF.GEO_SEGMENT GS,
FTCH_SRVC.CMTT_PURPOSE CMTTPURPOSE,
GLOBAL_SF.SCREENER_COMMITTEE_OUTCOME SCO,
(SELECT
COMMITTEE_ID COMMITTEE_ID
,MAX(DECODE(QUORUM_TITLE_ID, 1, SUBTOTAL, 0)) CHAIRVOTTOTAL
,MAX(DECODE(QUORUM_TITLE_ID, 2, SUBTOTAL, 0)) INDPTOTAL
,MAX(DECODE(QUORUM_TITLE_ID, 3, SUBTOTAL, 0)) VOTERTOTAL
,MAX(DECODE(QUORUM_TITLE_ID, 4, SUBTOTAL, 0)) NONVOTERTOTAL
,MAX(DECODE(QUORUM_TITLE_ID, 5, SUBTOTAL, 0)) PRIMANLYTOTAL
,MAX(DECODE(QUORUM_TITLE_ID, 6, SUBTOTAL, 0)) SECANLYTOTAL
,MAX(DECODE(QUORUM_TITLE_ID, 16, SUBTOTAL, 0)) OBSERVERTOTAL
,MAX(DECODE(QUORUM_TITLE_ID, 17, SUBTOTAL, 0)) CHAIRNVOTTOTAL
,MAX(DECODE(QUORUM_TITLE_ID, 18, SUBTOTAL, 0)) CHAIRINDTOTAL
FROM(
SELECT DCA.COMMITTEE_ID COMMITTEE_ID,
DCA.QUORUM_TITLE_ID QUORUM_TITLE_ID ,
COUNT(*) SUBTOTAL
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA
GROUP BY DCA.COMMITTEE_ID
,DCA.QUORUM_TITLE_ID )
GROUP BY COMMITTEE_ID) QTITLECNT
,(SELECT COMMITTEE_ID COMMITTEE_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';'))
KEEP(DENSE_RANK LAST ORDER BY RNT),';') PRIMARYANALYST
FROM
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID,
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT ,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA,
FII_CORE.EMP EMP
WHERE DCA.QUORUM_TITLE_ID = 5
AND DCA.EMPNO = EMP.EMPNO)
GROUP BY COMMITTEE_ID
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID
START WITH RNT = 1)PRIMAN
,(SELECT COMMITTEE_ID COMMITTEE_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';'))
KEEP(DENSE_RANK LAST ORDER BY RNT),';') CHAIRVOT
FROM
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID,
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT ,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA,
FII_CORE.EMP EMP
WHERE DCA.QUORUM_TITLE_ID = 1
AND DCA.EMPNO = EMP.EMPNO
)
GROUP BY COMMITTEE_ID
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID
START WITH RNT = 1)CHAIRVOT
,(SELECT COMMITTEE_ID COMMITTEE_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';'))
KEEP(DENSE_RANK LAST ORDER BY RNT),';') CHAIRNVOT
FROM
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID,
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT ,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA,
FII_CORE.EMP EMP
WHERE DCA.QUORUM_TITLE_ID = 17
AND DCA.EMPNO = EMP.EMPNO
)
GROUP BY COMMITTEE_ID
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID
START WITH RNT = 1)CHAIRNVOT
,(SELECT COMMITTEE_ID COMMITTEE_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';'))
KEEP(DENSE_RANK LAST ORDER BY RNT),';') CHAIRIND
FROM
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID,
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT ,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA,
FII_CORE.EMP EMP
WHERE DCA.QUORUM_TITLE_ID = 18
AND DCA.EMPNO = EMP.EMPNO
)
GROUP BY COMMITTEE_ID
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID
START WITH RNT = 1)CHAIRIND
,(SELECT COMMITTEE_ID COMMITTEE_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';'))
KEEP(DENSE_RANK LAST ORDER BY RNT),';') VOTER
FROM
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID,
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT ,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA,
FII_CORE.EMP EMP
WHERE DCA.QUORUM_TITLE_ID = 3
AND DCA.EMPNO = EMP.EMPNO
)
GROUP BY COMMITTEE_ID
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID
START WITH RNT = 1)VOTER
,(SELECT COMMITTEE_ID COMMITTEE_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';'))
KEEP(DENSE_RANK LAST ORDER BY RNT),';') SECANALYST
FROM
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID,
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT ,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA,
FII_CORE.EMP EMP
WHERE DCA.QUORUM_TITLE_ID = 6
AND DCA.EMPNO = EMP.EMPNO
)
GROUP BY COMMITTEE_ID
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID
START WITH RNT = 1)SECANALYST
,(SELECT COMMITTEE_ID COMMITTEE_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';'))
KEEP(DENSE_RANK LAST ORDER BY RNT),';') NONVOTER
FROM
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID,
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT ,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA,
FII_CORE.EMP EMP
WHERE DCA.QUORUM_TITLE_ID = 4
AND DCA.EMPNO = EMP.EMPNO
)
GROUP BY COMMITTEE_ID
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID
START WITH RNT = 1)NONVOTER
,(SELECT COMMITTEE_ID COMMITTEE_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';'))
KEEP(DENSE_RANK LAST ORDER BY RNT),';')INDPERSON
FROM
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID,
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT ,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA,
FII_CORE.EMP EMP
WHERE DCA.QUORUM_TITLE_ID = 2
AND DCA.EMPNO = EMP.EMPNO
)
GROUP BY COMMITTEE_ID
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID
START WITH RNT = 1)INDPERSON
,(SELECT COMMITTEE_ID COMMITTEE_ID,
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';'))
KEEP(DENSE_RANK LAST ORDER BY RNT),';') OBSERVER
FROM
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID,
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT ,
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA,
FII_CORE.EMP EMP
WHERE DCA.QUORUM_TITLE_ID = 16
AND DCA.EMPNO = EMP.EMPNO
)
GROUP BY COMMITTEE_ID
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID
START WITH RNT = 1)OBSERVER
WHERE
DC.COMMITTEE_TYPE_ID = CT.COMMITTEE_TYPE_ID
AND DC.DEAL_ID = DEAL.DEAL_ID
AND DEAL.BUS_GRP_ID = BG.BUS_GRP_ID
AND DEAL.GEO_SEGMENT_ID = GS.GEO_SEGMENT_ID (+)
AND DC.SCRNR_CMTE_OUTCOME_ID = SCO.SCRNR_CMTE_OUTCOME_ID (+)
AND DC.COMMITTEE_ID = PRIMAN.COMMITTEE_ID(+)
AND DC.COMMITTEE_ID = CHAIRVOT.COMMITTEE_ID(+)
AND DC.COMMITTEE_ID = CHAIRNVOT.COMMITTEE_ID(+)
AND DC.COMMITTEE_ID = CHAIRIND.COMMITTEE_ID(+)
AND DC.COMMITTEE_ID = VOTER.COMMITTEE_ID(+)
AND DC.COMMITTEE_ID = INDPERSON.COMMITTEE_ID(+)
AND DC.COMMITTEE_ID = NONVOTER.COMMITTEE_ID(+)
AND DC.COMMITTEE_ID = SECANALYST.COMMITTEE_ID(+)
AND DC.COMMITTEE_ID = OBSERVER.COMMITTEE_ID(+)
AND DEAL.COUNTRY_OF_ASSETS_ID = CNTRY.CNTRY_ID (+)
AND DC.COMMITTEE_PURPOSE_ID = CMTTPURPOSE.CMTT_PURPOSE_ID (+)
AND DC.ACTV_FLG = 'Y'
AND DC.COMMITTEE_ID = QTITLECNT.COMMITTEE_ID (+)
) TEMPTABLE
WHERE UPPER(GEO_SEGMENT) = UPPER('EMEA')
AND UPPER(COMMITTEETYPE) = UPPER('APPEAL')
AND COMMITTEE_DT >= '01 JUN 2010'
AND COMMITTEE_DT < '01 JUL 2010'
ORDER BY COMMITTEE_DT DESC
;