Clarity

I have a query that works well on SQL  (SWL 2012. We are a SQL on premise client, 14.2- Jaspersoft Studio 5.6.1) But, when I use this in the Jaspersoft Query, it fails on the row_number command. The only item I change is the date constraint in sql becomes a  parameter in Jaspersoft, but that was all working until I added in the row_number.   I am using the $P as the parameter for the date in our audit view table, thus when the report is run, the user inputs the Start and End date . Even though it gives an error, I did try running it, and indeed it fails.  I have researched this a lot, and I cannot find anything specific to this. I did post this on the Jaspersoft Community too, but so far no luck  Any ideas on how to make it work?

Jaspersoft query:

select a.oracle_project_id,
a.client_id, a.mhs_client, a.stage, a.Date_Changed

from (

select  o.oracle_project_id, o.client_id,
o.mhs_client, v.Date_Changed, stg.name stage,

ROW_NUMBER () OVER (PARTITION BY o.oracle_project_id ORDER BY v.Date_Changed desc) AS RowNumber)

from
inv_investments inv

inner join inv_projects p on inv.id = p.prid and p.is_template = 0 and p.IS_PROGRAM = 0

inner join odf_ca_project o on o.id = inv.id

inner join allscripts_oracle_project_audit_view v on v.Project_Code = inv.code and v.COLUMN_NAME = 'is_active' and v.System_Value_After = 0 and v.date_changed BETWEEN $P{start_date} AND $P{end_date}

LEFT OUTER JOIN CMN_LOOKUPS_V stg ON inv.stage_code =stg.lookup_code

     AND stg.lookup_type = 'INV_STAGE_TYPE'

     and stg.language_code ='en'

     and stg.is_active = '1'

    and stg.parent_lookup_code = 'mhs_stage'

inner join prj_obs_associations o2 on inv.ID = o2.RECORD_ID

and o2.table_name = 'SRM_PROJECTS'

and o2.unit_id in ( (select n.child_obs_unit_id from PRJ_OBS_UNITS o, NBI_DIM_OBS_FLAT n

where o.name  in ('Allscripts Services') and o.dept =2 and n.parent_obs_unit_id = o.id))

inner join (select o4.oracle_project_id from odf_ca_project o4 join srm_projects p2 on o4.id=p2.id where p2.is_program = 0

group by o4.oracle_project_id having sum (p2.is_active)= '0') o3 on o3.oracle_project_id=o.oracle_project_id

where o.oracle_project_id is not null

)  a

where
a.RowNumber = 1

  The Red X occurs on the ROW_NUMBER () OVER (PARTITION BY o.oracle_project_id ORDER BY v.Date_Changed desc) AS RowNumber) row and shows

Multiple markers at this line

- missing EOF at '('

- no viable alternative at input ')'

- missing ')' at 'OVER'

You don't appear to need the row_number approach because all you derive from that view is the latest (maximum) date.

instead of this:

inner join allscripts_oracle_project_audit_view v on v.Project_Code = inv.code and v.COLUMN_NAME = 'is_active' and v.System_Value_After = 0 and v.date_changed

BETWEEN $P{start_date} AND $P{end_date}

try this

      INNER JOIN (

            SELECT

                  av.Project_Code

                , MAX(av.date_changed) AS date_changed

            FROM allscripts_oracle_project_audit_view av

            WHERE av.COLUMN_NAME = 'is_active'

                  AND av.System_Value_After = 0

                  AND av.date_changed BETWEEN $P{start_date} AND $P{end_date}

            GROUP BY

                  av.Project_Code

      ) v ON v.Project_Code = inv.code

and remove row_number() and related where

SELECT

      o.oracle_project_id

    , o.client_id

    , o.mhs_client

    , v.Date_Changed

    , stg.name stage

FROM inv_investments inv

      INNER JOIN inv_projects p ON inv.id = p.prid

                  AND p.is_template = 0

                  AND p.IS_PROGRAM = 0

      INNER JOIN odf_ca_project o ON o.id = inv.id

      INNER JOIN (

            SELECT

                  av.Project_Code

                , MAX(av.date_changed) AS date_changed

            FROM allscripts_oracle_project_audit_view av

            WHERE av.COLUMN_NAME = 'is_active'

                  AND av.System_Value_After = 0

                  AND av.date_changed BETWEEN $P{start_date} AND $P{end_date}

            GROUP BY

                  av.Project_Code

      ) v ON v.Project_Code = inv.code

      LEFT OUTER JOIN CMN_LOOKUPS_V stg ON inv.stage_code = stg.lookup_code

                  AND stg.lookup_type = 'INV_STAGE_TYPE'

                  AND stg.language_code = 'en'

                  AND stg.is_active = '1'

                  AND stg.parent_lookup_code = 'mhs_stage'

      INNER JOIN prj_obs_associations o2 ON inv.ID = o2.RECORD_ID

                  AND o2.table_name = 'SRM_PROJECTS'

                  AND o2.unit_id IN (

                        (SELECT

                              n.child_obs_unit_id

                        FROM PRJ_OBS_UNITS o

                           , NBI_DIM_OBS_FLAT n

                        WHERE o.name IN ('Allscripts Services')

                              AND o.dept = 2

                              AND n.parent_obs_unit_id = o.id)

                  )

      INNER JOIN (

            SELECT

                  o4.oracle_project_id

            FROM odf_ca_project o4

                  JOIN srm_projects p2 ON o4.id = p2.id

            WHERE p2.is_program = 0

            GROUP BY

                  o4.oracle_project_id

            HAVING SUM(p2.is_active) = '0'

      ) o3 ON o3.oracle_project_id = o.oracle_project_id

WHERE o.oracle_project_id IS NOT NULL

=======================

tip:

Be VERY careful with how you define date ranges. Best practice is to avoid between for date ranges.

Much more reliable is >= plus <

e.g.

AND av.date_changed >= '2015-12-01' and av.date_changed < '2016-01-01'

For more on this topic please see: Beware of Between
the best practice with date and time ranges is to avoid BETWEEN and to always use the form:  WHERE col >= '20120101' AND col < '20120201' This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan

Thanks for the input.  However, without the row number syntax, I get duplicates. The max date is only reading the latest audit date of when the project was marked inactive, since a project could be reactivated and then activated again.  There is not a one to one match for the project data association (oracle project id can map to many clarity project ids and I only want the oracle id to show once) . For now, we created a view and are pulling it that way, but I would still like to know why this did not work. I saw OOTB reports using the row_number too.

For the dates, that is referencing the Jaspersoft parameters, and the dates must default blank.  So, I may be misunderstanding why a date has to default. If I want the parameter to default blank, and there must be a $P on that parameter in the sql (Jaspersoft Studio), then how would that be done using the above? I wrote it based on what Jaspersoft documentation advised.

Thanks again!

I see. Then that derived table should be grouped by oracle_project_id, but as I don't know what columns are in your audit view I can only make this suggestion

      INNER JOIN (

            SELECT

                  oracle_project_id

                , MAX(av.date_changed) AS date_changed

            FROM allscripts_oracle_project_audit_view av

            join(s) sufficient to get oracle_project_id

            WHERE av.COLUMN_NAME = 'is_active'

                  AND av.System_Value_After = 0

                  AND av.date_changed BETWEEN $P{start_date} AND $P{end_date}

            GROUP BY

                  oracle_project_id

      ) v ON v.oracle_project_id = o.oracle_project_id

Not sure why row_number() isn't working for you , it's certainly a technique I would want to use at times.