lynn.harris

Yes, so sorry. That was my fault. I had issues trying to get my post to take, so I did not catch that when I re-pasted the post. The last ) does not exist in the actual Jasper query. In SQL, it works (I just subsitute the $P parameter dates with actual query language), but once I added in the row_number in Jaspersoft, it stopped working the red X error reads Multiple markers at this line- missing EOF at '('- missing ')' at 'OVER'- no viable alternative at input ')'Again, my apologies, but I appreciate any insight. I am hoping version 5.6.1 can support this query? If it helps, this is the SQL command that works great : select a.oracle_project_id, a.client_id, a.mhs_client, a.stage, a.Date_Changedfrom (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 RowNumberfrom inv_investments invinner join inv_projects p on inv.id = p.prid and p.is_template = 0 and p.IS_PROGRAM = 0inner join odf_ca_project o on o.id = inv.idinner 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 '2016-01-01' and '2016-01-13'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 nwhere o.name in ('Allscripts Services')and o.depth = 2and 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 = 0group by o4.oracle_project_id having sum (p2.is_active)= '0') o3 on o3.oracle_project_id =o.oracle_project_idwhere o.oracle_project_id is not null) awhere a.RowNumber = 1

I have the below query that works perfect in SQl Server 2012 (the only change between SQL and Jaspersoft is the date paaramter), but when I put it into Jaspsersoft, it "fails' on the row_number part. It was working fine prior to tweaking the query to add in this condition. I need this condition so that I do not get duplicate records of the oracle_project_id. This is Jaspersoft 5.6.1 (we will be going to 6 soon, but we use this wtih CA PPM, so we need to coordinate it with the release that supports v6). Any idea on how to correct it? I have posted the error note that shows in the dataset and query after the 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.depth = 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 This is the syntax issue is shows by the Red X in the dataset and query: Multiple markers at this line - no viable alternative at input ')' - missing ')' at 'OVER' - missing EOF at '(' Do I need to do a sub report instead? (not an expert on how to do that, just saw that somewhere) Thanks in advance for any help!