AnsweredAssumed Answered

Problem with Jaspersoft not recongizing ROW_NUMBER() OVER (PARTITION BY SQL comand

Question asked by vtleogal2 on Jan 18, 2016
Latest reply on Jan 28, 2016 by paul.maxwell

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'

 

 

Outcomes