Using Parameters in the select section of the query

[yuj]

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

[yuj]

My question really is that the fiscal year of the report is calculated based on on_studydate, but when on_studydate is null, i want to use the parameter End_Date is calculate the fiscal year.

Please advise with your expertise and I will really appreciated.

Fang

[yama818]

If you don't mind, please let me know.
Maybe I'll notice something.

1. In the definition of the parameter 'End_Date', is the Class 'java.util.Date'?
2. Is the argument of function 'KCI_ONCORE_RW_UTILS.get_fiscal_yr' of type 'date'?
3. As a confirmation of isolating the problem, can you check if an error occurs when you simplify and execute the SQL as follows?
    SELECT KCI_ONCORE_RW_UTILS.get_fiscal_yr($P{End_Date})) as YR FROM DUAL