HI Expert, I have a report with a date range parameters as Start_Date and End_Date. I want to use either Start_Date or End_Date on the select part of the sql query to calculate fiscal year and in the group by clause to group total the enrollments. And I have trouble to run this sql on jasper report. Anyone has any ideas? Notice if I remove the parameters in the SQL using sysdate-600 and sysdate in place of Start_Date and End_Date, the report work just fine. Thanks for your help inadvance. Fang ----Detail error below: CASE 1 Sql Query is Select nvl2(a.on_studydate, KCI_ONCORE_RW_UTILS.get_fiscal_yr(a.on_studydate), KCI_ONCORE_RW_UTILS.get_fiscal_yr($P{End_Date})) as YR, b.mgmt_group_description mgmt_group, Sum(nvl(a.Enrollment,0)) Accrual from KCI_ONCORE_RW_UTILS.kci_sv_subject a, SV_PCL_MGMT_MGMTGROUP b Where a.protocol_id=b.protocol_id(+) and (a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_subject_treatment(a.protocol_id) = 'Therapeutic') and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_Affiliate_Only(a.protocol_id) = 'N' ) and b.primary='Y' and ( on_studydate is null or (on_studydate>=$P{Start_Date} and on_studydate <= $P{End_Date} ) ) and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.kci_ou_by_protocol_id(a.protocol_id) = 1) group by YR , b.mgmt_group_description --- Error as "Error Msg = ORA-00904: "YR": invalid identifier" -- log as below-------------- Caused by: Error : 904, Position : 776, Sql = Select nvl2(a.on_studydate, KCI_ONCORE_RW_UTILS.get_fiscal_yr(a.on_studydate), KCI_ONCORE_RW_UTILS.get_fiscal_yr(:1 )) as YR, b.mgmt_group_description mgmt_group, Sum(nvl(a.Enrollment,0)) Accrual from KCI_ONCORE_RW_UTILS.kci_sv_subject a, SV_PCL_MGMT_MGMTGROUP b Where a.protocol_id=b.protocol_id(+) and (a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_subject_treatment(a.protocol_id) = 'Therapeutic') and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_Affiliate_Only(a.protocol_id) = 'N' ) and b.primary='Y' and ( on_studydate is null or (on_studydate>=:2 and on_studydate <= :3 ) ) and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.kci_ou_by_protocol_id(a.protocol_id) = 1) group by YR , b.mgmt_group_description, OriginalSql = Select nvl2(a.on_studydate, KCI_ONCORE_RW_UTILS.get_fiscal_yr(a.on_studydate), KCI_ONCORE_RW_UTILS.get_fiscal_yr(?)) as YR, b.mgmt_group_description mgmt_group, Sum(nvl(a.Enrollment,0)) Accrual from KCI_ONCORE_RW_UTILS.kci_sv_subject a, SV_PCL_MGMT_MGMTGROUP b Where a.protocol_id=b.protocol_id(+) and (a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_subject_treatment(a.protocol_id) = 'Therapeutic') and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_Affiliate_Only(a.protocol_id) = 'N' ) and b.primary='Y' and ( on_studydate is null or (on_studydate>=? and on_studydate <= ? ) ) and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.kci_ou_by_protocol_id(a.protocol_id) = 1) group by YR , b.mgmt_group_description, Error Msg = ORA-00904: "YR": invalid identifier at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498) ... 21 more --------------------------------------------------------------------------------------- CASE 2 IF I replace YR on the group by as NVL2 function then the error as follows: Select nvl2(a.on_studydate, KCI_ONCORE_RW_UTILS.get_fiscal_yr(a.on_studydate), KCI_ONCORE_RW_UTILS.get_fiscal_yr($P{End_Date})) as YR, b.mgmt_group_description mgmt_group, Sum(nvl(a.Enrollment,0)) Accrual from KCI_ONCORE_RW_UTILS.kci_sv_subject a, SV_PCL_MGMT_MGMTGROUP b Where a.protocol_id=b.protocol_id(+) and (a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_subject_treatment(a.protocol_id) = 'Therapeutic') and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_Affiliate_Only(a.protocol_id) = 'N' ) and b.primary='Y' and ( on_studydate is null or (on_studydate>=$P{Start_Date} and on_studydate <= $P{End_Date} ) ) and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.kci_ou_by_protocol_id(a.protocol_id) = 1) group by nvl2(a.on_studydate, KCI_ONCORE_RW_UTILS.get_fiscal_yr(a.on_studydate), KCI_ONCORE_RW_UTILS.get_fiscal_yr($P{End_Date})) , b.mgmt_group_description --error as "Error Msg = ORA-00979: not a GROUP BY expression" --- logs below: Caused by: Error : 979, Position : 12, Sql = Select nvl2(a.on_studydate, KCI_ONCORE_RW_UTILS.get_fiscal_yr(a.on_studydate), KCI_ONCORE_RW_UTILS.get_fiscal_yr(:1 )) as YR, b.mgmt_group_description mgmt_group, Sum(nvl(a.Enrollment,0)) Accrual from KCI_ONCORE_RW_UTILS.kci_sv_subject a, SV_PCL_MGMT_MGMTGROUP b Where a.protocol_id=b.protocol_id(+) and (a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_subject_treatment(a.protocol_id) = 'Therapeutic') and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_Affiliate_Only(a.protocol_id) = 'N' ) and b.primary='Y' and ( on_studydate is null or (on_studydate>=:2 and on_studydate <= :3 ) ) and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.kci_ou_by_protocol_id(a.protocol_id) = 1) group by nvl2(a.on_studydate, KCI_ONCORE_RW_UTILS.get_fiscal_yr(a.on_studydate), KCI_ONCORE_RW_UTILS.get_fiscal_yr(:4 )) , b.mgmt_group_description, OriginalSql = Select nvl2(a.on_studydate, KCI_ONCORE_RW_UTILS.get_fiscal_yr(a.on_studydate), KCI_ONCORE_RW_UTILS.get_fiscal_yr(?)) as YR, b.mgmt_group_description mgmt_group, Sum(nvl(a.Enrollment,0)) Accrual from KCI_ONCORE_RW_UTILS.kci_sv_subject a, SV_PCL_MGMT_MGMTGROUP b Where a.protocol_id=b.protocol_id(+) and (a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_subject_treatment(a.protocol_id) = 'Therapeutic') and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.get_Affiliate_Only(a.protocol_id) = 'N' ) and b.primary='Y' and ( on_studydate is null or (on_studydate>=? and on_studydate <= ? ) ) and ( a.protocol_id is null or KCI_ONCORE_RW_UTILS.kci_ou_by_protocol_id(a.protocol_id) = 1) group by nvl2(a.on_studydate, KCI_ONCORE_RW_UTILS.get_fiscal_yr(a.on_studydate), KCI_ONCORE_RW_UTILS.get_fiscal_yr(?)) , b.mgmt_group_description, Error Msg = ORA-00979: not a GROUP BY expression at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498) ... 21 more
