Sub Query "EXCEPT" Clause not working

0

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?

camnott's picture
43
Joined: Mar 10 2017 - 6:24am
Last seen: 10 hours 21 min ago

3 Answers:

0

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.

camnott's picture
43
Joined: Mar 10 2017 - 6:24am
Last seen: 10 hours 21 min ago
0

You haven't specified which database you are using.

Database such as MySQL do not support EXCEPT.

hozawa's picture
13952
Joined: Apr 24 2010 - 4:31pm
Last seen: 5 hours 21 min ago
0

If you are refering to what type of database, it is a sql server 2008. and this query runs perfect in SQL Server Management Studio.

It almost seems like the parameter is not being passed to the sub query.

I will do some further testing.

camnott's picture
43
Joined: Mar 10 2017 - 6:24am
Last seen: 10 hours 21 min ago
Feedback
randomness