asked on need help with error ORA_24323
I have a select statement with multiple UNION ALL statements and I keep gettign ORA-24323 orror message . The error points to the first line in the top SELECT... What confuses me is s to why the code with run in SQL NAVIGATOR and generate results, but will not pass valdiation in ORACLE REPORTS. Its in ORACLE REPORTS that I get this ORA-24323 error and dont know why..
HERE IS THE CODE::::::::::::::::::::::
SELECT WHICH_QUERY,
to_char(COUNT (*)) TOTAL_COUNT,
USERID,
FULLNAME,
REV_TYPE,
TREND,
TYPE_INDICATOR,
CONTRACTOR_ID,
CONTRACTOR_DESC,
CASE
WHEN WHICH_QUERY = 'A' THEN
CASE
WHEN (REV_TYPE = 'E' AND TREND ='RO')
THEN '21210'
WHEN (REV_TYPE = 'E' AND TREND <>'RO')
THEN 'Trend Code Not - RO'
WHEN (REV_TYPE = 'E' AND TREND IS NULL)
THEN 'No Trend Code - RO'
WHEN REV_TYPE = 'T'
THEN 'No CAFM Code'
WHEN REV_TYPE = 'R'
THEN '21220'
WHEN REV_TYPE = 'A' THEN
CASE
WHEN TYPE_INDICATOR <> 'N' THEN '21010'
WHEN TYPE_INDICATOR = 'N' THEN '21221'
WHEN TYPE_INDICATOR IS NULL THEN '21221'
ELSE
NULL
END
WHEN REV_TYPE IS NULL
THEN 'No Rev Type'
END
WHEN WHICH_QUERY = 'B' THEN
CASE
WHEN (REV_TYPE = 'E' AND TREND ='RO')
THEN '21210'
WHEN (REV_TYPE = 'E' AND TREND <>'RO')
THEN 'Trend Code Not - RO'
WHEN (REV_TYPE = 'E' AND TREND IS NULL)
THEN 'No Trend Code - RO'
WHEN (REV_TYPE = 'T' AND TYPE_INDICATOR = 'F')
THEN 'No CAFM Code'
WHEN (REV_TYPE = 'T' AND TYPE_INDICATOR = 'M')
THEN '21222'
WHEN REV_TYPE = 'R'
THEN '21220'
WHEN REV_TYPE = 'A'
THEN '21222'
WHEN REV_TYPE IS NULL
THEN 'No Rev Type'
END
WHEN WHICH_QUERY = 'C' THEN
CASE
WHEN (REV_TYPE = 'E' AND TREND ='RO')
THEN '21210'
WHEN (REV_TYPE = 'E' AND TREND <>'RO')
THEN 'Trend Code Not - RO'
WHEN (REV_TYPE = 'E' AND TREND IS NULL)
THEN 'No Trend Code - RO'
WHEN REV_TYPE = 'T'
THEN 'No CAFM Code'
WHEN REV_TYPE = 'P'
THEN '21220'
WHEN REV_TYPE = 'A'
THEN '21221'
WHEN REV_TYPE = 'R'
THEN '21010'
WHEN REV_TYPE IS NULL
THEN 'No Rev Type'
END
END AS REPORT_CAFM_CODE,
CASE
WHEN WHICH_QUERY IN ('A', 'B','C') THEN
CASE
WHEN REV_TYPE = 'E' THEN 'Reopen'
WHEN REV_TYPE = 'T' THEN 'Complex Other'
WHEN REV_TYPE = 'R' THEN 'Probe'
WHEN REV_TYPE = 'E' THEN 'Edit'
END
END AS REPORT_TYPE_REV,
QTYPE
FROM (
SELECT
'A' WHICH_QUERY,
NULL TOTAL_COUNT,
a.userid USERID,
lname||', '||fname FULLNAME,
b.type_postpay REV_TYPE,
d.trend_code TREND,
d.demand_ind TYPE_INDICATOR,
b.ctrr_id CONTRACTOR_ID,
ctr.ctrr_tx CONTRACTOR_DESC,
null REPORT_CAFM_CODE,
null REPORT_TYPE_REV,
'Pre-Pay' QTYPE
FROM mr.prepay_dec d,
mr.prepay_audit a,
cmg.employee@bisdb e,
mr.adr_req b,
prv.ctrr ctr
WHERE d.case_no = a.case_no
and d.case_no = b.case_no
and b.type_rev = 'R'
AND D.DT_DEC BETWEEN TO_DATE('01/01/2008','MM/D
AND TO_DATE('07/01/2008','MM/D
and a.occurrence=(select max(occurrence)
from mr.prepay_audit a1
where a1.case_no=a.case_no
and userid in ('LIDVBVZ','LIDWWII',
'LID31EE',
'LIDHDZZ','LIDUE9U',
'LIDC79A',
'LIDE5C2','LIDWAGS',
'LIDZTR2','LID2ZBH',
'LIDG9AE',
'LIDSIY3','LIDFDBJ',
'LIDPPDL','LIDJ31Z')
)
and a.userid = e.userid(+)
and B.CTRR_ID = CTR.CTRR_ID
group by a.userid,
lname||', '||fname,
b.type_postpay,
d.trend_code,
d.demand_ind,
B.CTRR_ID,
CTR.CTRR_TX
UNION ALL
SELECT
'B' WHICH_QUERY,
NULL TOTAL_COUNT,
a.userid USERID,
lname||', '||fname FULLNAME,
b.type_postpay REV_TYPE,
d.trend_code TREND,
TYPE_ind TYPE_INDICATOR,
b.ctrr_id CONTRACTOR_ID,
ctr.ctrr_tx CONTRACTOR_DESC,
null REPORT_CAFM_CODE,
null REPORT_TYPE_REV,
'Post-Pay' QTYPE
FROM mr.prepay_dec d,
mr.prepay_audit a,
cmg.employee@bisdb e,
mr.adr_req b,
prv.ctrr ctr
WHERE d.case_no = a.case_no
and d.case_no = b.case_no
and b.type_rev = 'F'
AND D.DT_DEC BETWEEN TO_DATE('01/01/2008','MM/D
AND TO_DATE('07/01/2008','MM/D
and a.occurrence=(select max(occurrence)
from mr.prepay_audit a1
where a1.case_no=a.case_no
and userid in ('LIDVBVZ','LIDWWII',
'LID31EE',
'LIDHDZZ','LIDUE9U',
'LIDC79A',
'LIDE5C2','LIDWAGS',
'LIDZTR2','LID2ZBH',
'LIDG9AE',
'LIDSIY3','LIDFDBJ',
'LIDPPDL','LIDJ31Z')
)
and a.userid = e.userid(+)
and B.CTRR_ID = CTR.CTRR_ID
group by a.userid,
lname||', '||fname,
b.type_postpay,
d.trend_code,
TYPE_ind,
B.CTRR_ID,
CTR.CTRR_TX
UNION ALL
SELECT
'C' WHICH_QUERY,
null TOTAL_COUNT,
a.userid USERID,
lname||', '||fname FULLNAME,
b.type_rev REV_TYPE,
d.trend_code TREND,
null TYPE_INDICATOR,
b.ctrr_id CONTRACTOR_ID,
ctr.ctrr_tx CONTRACTOR_DESC,
null REPORT_CAFM_CODE,
null REPORT_TYPE_REV,
'SNF' QTYPE
FROM mr.snf_pps_dec d,
mr.snf_pps_audit a,
cmg.employee@bisdb e,
mr.snf_pps_req b,
prv.ctrr ctr
WHERE d.seq# = a.seq#
and d.seq# = b.case_no
AND D.DT_DEC BETWEEN TO_DATE('01/01/2008','MM/D
AND TO_DATE('07/01/2008','MM/D
and a.occurrence=(select max(occurrence)
from mr.snf_pps_audit a1
where a1.seq#=a.seq#
and userid in ('LIDVBVZ','LIDWWII',
'LID31EE',
'LIDHDZZ','LIDUE9U',
'LIDC79A',
'LIDE5C2','LIDWAGS',
'LIDZTR2','LID2ZBH',
'LIDG9AE',
'LIDSIY3','LIDFDBJ',
'LIDPPDL','LIDJ31Z')
)
and a.userid = e.userid(+)
and B.CTRR_ID = CTR.CTRR_ID
GROUP BY a.userid,
lname||', '||fname,
b.type_rev,
d.trend_code,
TYPE_ind,
B.CTRR_ID,
CTR.CTRR_TX
)
GROUP BY
WHICH_QUERY,
USERID,
FULLNAME,
REV_TYPE,
TREND,
TYPE_INDICATOR,
QTYPE,
CONTRACTOR_ID,
CONTRACTOR_DESC,
REPORT_CAFM_CODE,
REPORT_TYPE_REV
HERE IS THE CODE::::::::::::::::::::::
SELECT WHICH_QUERY,
to_char(COUNT (*)) TOTAL_COUNT,
USERID,
FULLNAME,
REV_TYPE,
TREND,
TYPE_INDICATOR,
CONTRACTOR_ID,
CONTRACTOR_DESC,
CASE
WHEN WHICH_QUERY = 'A' THEN
CASE
WHEN (REV_TYPE = 'E' AND TREND ='RO')
THEN '21210'
WHEN (REV_TYPE = 'E' AND TREND <>'RO')
THEN 'Trend Code Not - RO'
WHEN (REV_TYPE = 'E' AND TREND IS NULL)
THEN 'No Trend Code - RO'
WHEN REV_TYPE = 'T'
THEN 'No CAFM Code'
WHEN REV_TYPE = 'R'
THEN '21220'
WHEN REV_TYPE = 'A' THEN
CASE
WHEN TYPE_INDICATOR <> 'N' THEN '21010'
WHEN TYPE_INDICATOR = 'N' THEN '21221'
WHEN TYPE_INDICATOR IS NULL THEN '21221'
ELSE
NULL
END
WHEN REV_TYPE IS NULL
THEN 'No Rev Type'
END
WHEN WHICH_QUERY = 'B' THEN
CASE
WHEN (REV_TYPE = 'E' AND TREND ='RO')
THEN '21210'
WHEN (REV_TYPE = 'E' AND TREND <>'RO')
THEN 'Trend Code Not - RO'
WHEN (REV_TYPE = 'E' AND TREND IS NULL)
THEN 'No Trend Code - RO'
WHEN (REV_TYPE = 'T' AND TYPE_INDICATOR = 'F')
THEN 'No CAFM Code'
WHEN (REV_TYPE = 'T' AND TYPE_INDICATOR = 'M')
THEN '21222'
WHEN REV_TYPE = 'R'
THEN '21220'
WHEN REV_TYPE = 'A'
THEN '21222'
WHEN REV_TYPE IS NULL
THEN 'No Rev Type'
END
WHEN WHICH_QUERY = 'C' THEN
CASE
WHEN (REV_TYPE = 'E' AND TREND ='RO')
THEN '21210'
WHEN (REV_TYPE = 'E' AND TREND <>'RO')
THEN 'Trend Code Not - RO'
WHEN (REV_TYPE = 'E' AND TREND IS NULL)
THEN 'No Trend Code - RO'
WHEN REV_TYPE = 'T'
THEN 'No CAFM Code'
WHEN REV_TYPE = 'P'
THEN '21220'
WHEN REV_TYPE = 'A'
THEN '21221'
WHEN REV_TYPE = 'R'
THEN '21010'
WHEN REV_TYPE IS NULL
THEN 'No Rev Type'
END
END AS REPORT_CAFM_CODE,
CASE
WHEN WHICH_QUERY IN ('A', 'B','C') THEN
CASE
WHEN REV_TYPE = 'E' THEN 'Reopen'
WHEN REV_TYPE = 'T' THEN 'Complex Other'
WHEN REV_TYPE = 'R' THEN 'Probe'
WHEN REV_TYPE = 'E' THEN 'Edit'
END
END AS REPORT_TYPE_REV,
QTYPE
FROM (
SELECT
'A' WHICH_QUERY,
NULL TOTAL_COUNT,
a.userid USERID,
lname||', '||fname FULLNAME,
b.type_postpay REV_TYPE,
d.trend_code TREND,
d.demand_ind TYPE_INDICATOR,
b.ctrr_id CONTRACTOR_ID,
ctr.ctrr_tx CONTRACTOR_DESC,
null REPORT_CAFM_CODE,
null REPORT_TYPE_REV,
'Pre-Pay' QTYPE
FROM mr.prepay_dec d,
mr.prepay_audit a,
cmg.employee@bisdb e,
mr.adr_req b,
prv.ctrr ctr
WHERE d.case_no = a.case_no
and d.case_no = b.case_no
and b.type_rev = 'R'
AND D.DT_DEC BETWEEN TO_DATE('01/01/2008','MM/D
AND TO_DATE('07/01/2008','MM/D
and a.occurrence=(select max(occurrence)
from mr.prepay_audit a1
where a1.case_no=a.case_no
and userid in ('LIDVBVZ','LIDWWII',
'LID31EE',
'LIDHDZZ','LIDUE9U',
'LIDC79A',
'LIDE5C2','LIDWAGS',
'LIDZTR2','LID2ZBH',
'LIDG9AE',
'LIDSIY3','LIDFDBJ',
'LIDPPDL','LIDJ31Z')
)
and a.userid = e.userid(+)
and B.CTRR_ID = CTR.CTRR_ID
group by a.userid,
lname||', '||fname,
b.type_postpay,
d.trend_code,
d.demand_ind,
B.CTRR_ID,
CTR.CTRR_TX
UNION ALL
SELECT
'B' WHICH_QUERY,
NULL TOTAL_COUNT,
a.userid USERID,
lname||', '||fname FULLNAME,
b.type_postpay REV_TYPE,
d.trend_code TREND,
TYPE_ind TYPE_INDICATOR,
b.ctrr_id CONTRACTOR_ID,
ctr.ctrr_tx CONTRACTOR_DESC,
null REPORT_CAFM_CODE,
null REPORT_TYPE_REV,
'Post-Pay' QTYPE
FROM mr.prepay_dec d,
mr.prepay_audit a,
cmg.employee@bisdb e,
mr.adr_req b,
prv.ctrr ctr
WHERE d.case_no = a.case_no
and d.case_no = b.case_no
and b.type_rev = 'F'
AND D.DT_DEC BETWEEN TO_DATE('01/01/2008','MM/D
AND TO_DATE('07/01/2008','MM/D
and a.occurrence=(select max(occurrence)
from mr.prepay_audit a1
where a1.case_no=a.case_no
and userid in ('LIDVBVZ','LIDWWII',
'LID31EE',
'LIDHDZZ','LIDUE9U',
'LIDC79A',
'LIDE5C2','LIDWAGS',
'LIDZTR2','LID2ZBH',
'LIDG9AE',
'LIDSIY3','LIDFDBJ',
'LIDPPDL','LIDJ31Z')
)
and a.userid = e.userid(+)
and B.CTRR_ID = CTR.CTRR_ID
group by a.userid,
lname||', '||fname,
b.type_postpay,
d.trend_code,
TYPE_ind,
B.CTRR_ID,
CTR.CTRR_TX
UNION ALL
SELECT
'C' WHICH_QUERY,
null TOTAL_COUNT,
a.userid USERID,
lname||', '||fname FULLNAME,
b.type_rev REV_TYPE,
d.trend_code TREND,
null TYPE_INDICATOR,
b.ctrr_id CONTRACTOR_ID,
ctr.ctrr_tx CONTRACTOR_DESC,
null REPORT_CAFM_CODE,
null REPORT_TYPE_REV,
'SNF' QTYPE
FROM mr.snf_pps_dec d,
mr.snf_pps_audit a,
cmg.employee@bisdb e,
mr.snf_pps_req b,
prv.ctrr ctr
WHERE d.seq# = a.seq#
and d.seq# = b.case_no
AND D.DT_DEC BETWEEN TO_DATE('01/01/2008','MM/D
AND TO_DATE('07/01/2008','MM/D
and a.occurrence=(select max(occurrence)
from mr.snf_pps_audit a1
where a1.seq#=a.seq#
and userid in ('LIDVBVZ','LIDWWII',
'LID31EE',
'LIDHDZZ','LIDUE9U',
'LIDC79A',
'LIDE5C2','LIDWAGS',
'LIDZTR2','LID2ZBH',
'LIDG9AE',
'LIDSIY3','LIDFDBJ',
'LIDPPDL','LIDJ31Z')
)
and a.userid = e.userid(+)
and B.CTRR_ID = CTR.CTRR_ID
GROUP BY a.userid,
lname||', '||fname,
b.type_rev,
d.trend_code,
TYPE_ind,
B.CTRR_ID,
CTR.CTRR_TX
)
GROUP BY
WHICH_QUERY,
USERID,
FULLNAME,
REV_TYPE,
TREND,
TYPE_INDICATOR,
QTYPE,
CONTRACTOR_ID,
CONTRACTOR_DESC,
REPORT_CAFM_CODE,
REPORT_TYPE_REV
Oracle Database
Last Comment
Shaju Kumbalath
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
there is chance u r not connected with database while running the report or the number of rows retrieved by query is huge