Excel VBA OraOLEDB & Oracle PIVOT Function

RoelofvH

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= NothingEnd 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_COUNTFROM 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