Jump to content
Changes to the Jaspersoft community edition download ×

Sub Query "EXCEPT" Clause not working


camnott
Go to solution Solved by camnott,

Recommended Posts

I have an EXCEPT clause in a sub query, it doesn't seem to be working...

SQL:

 

SELECT        INV.ID   [investment Key]
                    , INV.CODE [investment ID]
                    , INV.NAME [investment Name]
                    , RES.FULL_NAME [investment Manager]
                    , STAGE.NAME    [stage]
                    , PHASE.NAME    [Phase]
FROM           niku.INV_INVESTMENTS  INV
LEFT JOIN    niku.SRM_RESOURCES    RES    ON     RES.USER_ID = INV.MANAGER_ID
LEFT JOIN    niku.ODF_CA_PROJECT   ODP    ON     INV.ID      = ODP.id
LEFT JOIN    (
              SELECT        *
              FROM          niku.CMN_LOOKUPS_V               LKV
              WHERE         LKV.LOOKUP_TYPE   = 'INV_STAGE_TYPE' 
              AND           LKV.LANGUAGE_CODE = 'en'
             ) STAGE  ON  stage.LOOKUP_CODE = INV.STAGE_CODE
             LEFT JOIN    (
              SELECT        *
              FROM          niku.CMN_LOOKUPS_V LKV
              WHERE         LKV.LOOKUP_TYPE   = '******* <Intentionally Blocked out> *****_SDLC_PHASES' 
              AND           LKV.LANGUAGE_CODE = 'en'
             ) PHASE  ON    ODP.******* <Intentionally Blocked out> *****_sdlc_phase = PHASE.LOOKUP_CODE
INNER JOIN   ( 
              SELECT        INV.ID  [investment ID]
              FROM          niku.INV_INVESTMENTS INV
              WHERE         INV.STATUS = 1
              AND           INV.IS_ACTIVE = 1
              AND           INV.CODE LIKE 'PRJ_%'
              AND           INV.ENTITY_CODE LIKE ******* <Intentionally Blocked out> *****

              EXCEPT 

              SELECT        SRP.ODF_PARENT_ID [investment ID]
              FROM          niku.ODF_CA_COP_PRJ_STATUSRPT SRP
              WHERE         $X{IN, SRP.cop_report_date, TIME_PERIOD}
            ) Reports ON INV.ID = Reports.[investment ID]
            WHERE    $X{IN, res.FULL_NAME, INV_MANAGER}
            AND      $X{IN, INV.CODE, INV_ID}
            ORDER BY [investment Manager]

Report runs perfectly, with one issue... The Except Clause. 

Any Idea's?

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

  • Solution

Found the issue....

WHERE      $X{IN, SRP.cop_report_date, TIME_PERIOD}

So.... I had this parameter as a collection. Got rid of this, and created two new parameters. Period Start and Period End, Changed line to

WHERE     SRP.cop_report_date BETWEEN $P{PERIOD_START} AND $P{PERIOD_END}

Works like a charm.  I guess I should have done it this way originally. Was just trying to make it easier.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...