Jump to content

Using Parameters in the select section of the query


yuj

Recommended Posts

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

Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...