none
SQL pass-through queries don't always work properly in PowerPivot

    Question

  • Hi there,

    I have been having problems importing SQL query data into PowerPivot, especially when the query has several joins or unions in it.  The queries import into regular Excel via Microsoft Query with no issues whatsoever, and they also work perfectly in Aqua Data Studio and Oracle SQL Developer.

    Following is a simple example of a query that doesn't work properly.  The WHERE clause clearly specifies data only from 2007 to sysdate-1, but PowerPivot imports all the data in the database, going all the way back to 1996!  Clearly, I think, there is a problem with PowerPivot's SQL handling.

        select (HOURENDING_DATE + (HOURENDING-1)/24) as Date_Time, ACT_POSTED_PP as Pool_Price
        from enersup_owner.actual_forecast
        where HOURENDING_DATE between '01Jan07' and trunc(sysdate, 'DD')-1
        order by HOURENDING_DATE, HOURENDING


    Am I doing something wrong?  I don't think so.

    Thanks in advance,
    Sean

    Tuesday, October 01, 2013 3:17 PM

Answers

  • If you change your date format to '2007-01-01' instead of '01Jan07', do you get the same results? 

    Also, can you share what version of PowerPivot you're working with?  And what version of Oracle you're sourcing from?


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Wednesday, October 02, 2013 5:15 AM

All replies

  • If you change your date format to '2007-01-01' instead of '01Jan07', do you get the same results? 

    Also, can you share what version of PowerPivot you're working with?  And what version of Oracle you're sourcing from?


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Wednesday, October 02, 2013 5:15 AM
  • That totally worked; thanks a lot Brent!  :D

    I'm using Excel 2010 PowerPivot version 11.0.3000.0 on Oracle 10g2.


    • Edited by seanrez Wednesday, October 02, 2013 2:43 PM added Excel 2010
    Wednesday, October 02, 2013 2:42 PM