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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,312 views