I am passing a query via Excel VBA OraOLEDB data provider to an Oracle 11.2g DB but its only returning partial results. The query has been validated in SQL Developer and works 100%. The OraOLEDB also works fine returning results for simple SELECT queries against the same Oracle 11.2g DB.
Does anyone possibly have any idea why the full set is not populating? Your help will be greatly appreciated
VBA (Sensitive info taken out of connection string)
Sub RunQuery()
SrvrInfo = "Provider=OraOLEDB.Oracle;" & _
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostaddress)(PORT=portnumber))(CONNECT_DATA=(SERVICE_NAME = servicename) (SERVER=DEDICATED)));User Id=user_id;Password=psswrd;"
Set Connct= New ADODB.Connection
With Connct
.CursorLocation = adUseClient
.Open SrvrInfo
.CommandTimeout = 0
Set RcrdSet = .Execute(SQLStmnt) ''See SQL query below
End With
aSpreadsheetRange.CopyFromRecordset RcrdSet
RcrdSet.Close
Connct.Close
Set RcrdSet= Nothing
Set Connct= Nothing
End Sub
SQL Query (SQLStmnt variable in VBA above)
SELECT DISTINCT *
FROM (
SELECT
(CASE
WHEN A.AGE < 15 THEN '1. < 15'
WHEN A.AGE >= 15 AND A.AGE <= 24 THEN '2. 15 to 24'
WHEN A.AGE >= 25 AND A.AGE <= 34 THEN '3. 25 to 34'
WHEN A.AGE >= 35 AND A.AGE <= 44 THEN '4. 35 to 44'
WHEN A.AGE >= 45 AND A.AGE <= 54 THEN '5. 45 to 54'
WHEN A.AGE >= 55 AND A.AGE <= 64 THEN '6. 55 to 64'
WHEN A.AGE >= 65 AND A.AGE <= 74 THEN '7. 65 to 74'
WHEN A.AGE > 74 THEN '8. > 74'
ELSE 'NO AGE'
END) BUCKET,
A.BONUS_MONTH
Count(*) AGE_COUNT
FROM EMPLOYEE A
GROUP BY
(CASE
WHEN A.AGE < 15 THEN '1. < 15'
WHEN A.AGE >= 15 AND A.AGE <= 24 THEN '2. 15 to 24'
WHEN A.AGE >= 25 AND A.AGE <= 34 THEN '3. 25 to 34'
WHEN A.AGE >= 35 AND A.AGE <= 44 THEN '4. 35 to 44'
WHEN A.AGE >= 45 AND A.AGE <= 54 THEN '5. 45 to 54'
WHEN A.AGE >= 55 AND A.AGE <= 64 THEN '6. 55 to 64'
WHEN A.AGE >= 65 AND A.AGE <= 74 THEN '7. 65 to 74'
WHEN A.AGE > 74 THEN '8. > 74'
ELSE 'NO AGE'
END)
) B
PIVOT (SUM(AGE_COUNT) FOR BONUS_MONTH IN ('22/JUL/2016','22/AUG/2016'))
ORDER BY BUCKET
The result populating in Excel is (Headers pre-populated by myself and not originating from cursor)
BUCKET | '22/JUL/2016' | '22/AUG/2016' |
1. < 15 | | |
2. 15 to 24 | | |
3. 25 to 34 | | |
4. 35 to 44 | | |
5. 45 to 54 | | |
6. 55 to 64 | | |
7. 65 to 74 | | |
8. > 74 | | |
NO AGE | | |