Sub Query "EXCEPT" Clause not working

[camnott]

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?

[hozawa]

You haven't specified which database you are using.

Database such as MySQL do not support EXCEPT.

[camnott]

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]

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.