Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Excel VBA OraOLEDB & Oracle PIVOT Function

RoelofvHNov 29 2016 — edited Nov 29 2016

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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 27 2016
Added on Nov 29 2016
0 comments
7,432 views