yuj
-
Posts
2 -
Joined
-
Last visited
Content Type
Forum
Downloads
Featured Visualizations
Knowledge Base
Documentation (PDF Downloads)
Blog
Documentation (Test Area)
Documentation
Dr. Jaspersoft Webinar Series
Security Advisories
Events
Profiles
Posts posted by 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 identifierat 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 expressionat oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
... 21 more
Using Parameters in the select section of the query
in Products
Posted
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