[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT ''Reply'' BIX_EMC_OUTCOME,
''Email Sent'' BIX_EMC_RESULT,
''General Inquiry'' BIX_EMC_REASON,
7390 BIX_EMC_COUNT,
7390 BIX_PMV_TOTAL1,
8.5 BIX_EMC_PERTOTAL1,
100 BIX_PMV_TOTAL2,
4 BIX_EMC_COUNTCHANGE,
4 BIX_PMV_TOTAL3
FROM DUAL';
'SELECT * FROM(
SELECT
outcome.outcome_code BIX_EMC_OUTCOME,
result.result_code BIX_EMC_RESULT,
reason.reason_code BIX_EMC_REASON,
NVL(curr_count,0) BIX_EMC_COUNT,
NVL(SUM(curr_count) OVER(),0) BIX_PMV_TOTAL1,
NVL(curr_count,0) * 100/DECODE(curr_outcount,0,NULL,curr_outcount) BIX_EMC_PERTOTAL1,
(NVL(curr_count,0) * 100/DECODE(curr_outcount,0,NULL,curr_outcount)) -
(NVL(prev_count,0) * 100/DECODE(prev_outcount,0,NULL,prev_outcount)) BIX_EMC_COUNTCHANGE
FROM
(
SELECT outcome_id,
result_id,
reason_id,
grouping_id(outcome_id,result_id,reason_id) g_id,
SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)) curr_count,
SUM(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)) prev_count,
sum(SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)))
over (partition by outcome_id) curr_outcount,
sum(SUM(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)))
over (partition by outcome_id) prev_outcount
FROM bix_email_details_mv fact,
fii_time_rpt_struct cal
WHERE fact.time_id = cal.time_id
AND fact.row_type = :l_row_type
AND fact.period_type_id = cal.period_type_id
AND fact.outcome_id != :l_outcome_filter
AND cal.report_date IN ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
' || l_where_clause ||
' GROUP BY
outcome_id,result_id,reason_id
) fact, jtf_ih_outcomes_vl outcome,jtf_ih_results_vl result,jtf_ih_reasons_vl reason
WHERE fact.outcome_id = outcome.outcome_id(+)
AND fact.result_id = result.result_id(+)
AND fact.reason_id = reason.reason_id(+)
ORDER BY outcome.outcome_code,g_id,result.result_code,reason.reason_code
) --start 002
WHERE ABS(NVL(bix_emc_count,0))+ABS(NVL(bix_emc_countchange,0)) != 0 ';
' SELECT
DECODE(g_id,3,:l_subtotal,outcome.outcome_code) BIX_EMC_OUTCOME,
DECODE(g_id,3,NULL,NVL(result.result_code,:l_unknown)) BIX_EMC_RESULT,
DECODE(g_id,3,NULL,NVL(reason.reason_code,:l_unknown )) BIX_EMC_REASON,
NVL(curr_count,0) BIX_EMC_COUNT,
NVL(SUM(curr_count) OVER(),0) BIX_PMV_TOTAL1,
NVL(curr_count,0) * 100/DECODE(SUM(curr_count) OVER(),0,NULL,SUM(curr_count) OVER()) BIX_EMC_PERTOTAL1,
NVL(curr_count,0) * 100/DECODE(SUM(curr_count) OVER(),0,NULL,SUM(curr_count) OVER()) -
NVL(prev_count,0) * 100/DECODE(SUM(prev_count) OVER(),0,NULL,SUM(prev_count) OVER()) BIX_EMC_COUNTCHANGE
FROM
(
SELECT outcome_id,
result_id,
reason_id,
grouping_id(outcome_id,result_id,reason_id) g_id,
SUM(DECODE(cal.report_date,&BIS_CURRENT_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)) curr_count,
SUM(DECODE(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,EMAILS_ORR_COUNT_IN_PERIOD,NULL)) prev_count
FROM bix_email_details_mv fact,
fii_time_rpt_struct cal
WHERE fact.time_id = cal.time_id
AND fact.row_type = :l_row_type
AND fact.period_type_id = cal.period_type_id
AND fact.outcome_id != :l_outcome_filter
AND cal.report_date IN ( &BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN)=cal.record_type_id
' || l_where_clause ||
' GROUP BY
GROUPING SETS
(
( outcome_id,result_id,reason_id),
( outcome_id)
)
) fact, jtf_ih_outcomes_vl outcome,jtf_ih_results_vl result,jtf_ih_reasons_vl reason
WHERE fact.outcome_id = outcome.outcome_id(+)
AND fact.result_id = result.result_id(+)
AND fact.reason_id = reason.reason_id(+) ';