[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NULL AS VIEWBY,
NULL AS ENI_MEASURE30,
NULL AS ENI_MEASURE1,
NULL AS ENI_MEASURE9,
NULL AS ENI_MEASURE3,
NULL AS ENI_MEASURE6,
NULL AS ENI_MEASURE7,
NULL AS ENI_MEASURE11,
NULL AS ENI_MEASURE31,
NULL AS ENI_MEASURE32,
NULL AS ENI_MEASURE33,
NULL AS ENI_MEASURE34,
NULL AS ENI_MEASURE20,
NULL AS ENI_MEASURE21,
NULL AS ENI_MEASURE22,
NULL AS ENI_MEASURE23,
NULL AS ENI_MEASURE25,
NULL AS ENI_MEASURE26,
NULL AS ENI_MEASURE27,
NULL AS ENI_MEASURE28,
NULL AS ENI_MEASURE36,
NULL AS ENI_MEASURE37,
NULL AS ENI_MEASURE38
FROM DUAL';
select value as VIEWBY
,id as VIEWBYID
,NULL as ENI_MEASURE30
,curr_open_cnt as ENI_MEASURE1
,prev_open_cnt as ENI_MEASURE9
,curr_open_days_cnt/DECODE(curr_open_cnt,0,NULL,curr_open_cnt) as ENI_MEASURE3
,prev_open_days_cnt/DECODE(prev_open_cnt,0,NULL,prev_open_cnt) as ENI_MEASURE11
,curr_past_open_days_cnt/DECODE(curr_open_cnt,0,NULL,curr_open_cnt) as ENI_MEASURE6
,prev_past_open_days_cnt/DECODE(prev_open_cnt,0,NULL,prev_open_cnt) as ENI_MEASURE7
,avg1_cnt as ENI_MEASURE31
,avg2_cnt as ENI_MEASURE32
,avg3_cnt as ENI_MEASURE33
,avg4_cnt as ENI_MEASURE34
,SUM(curr_open_cnt) OVER() as ENI_MEASURE20,
(
(SUM(curr_open_cnt) OVER() - SUM(prev_open_cnt) OVER())
/(DECODE(SUM(prev_open_cnt) OVER(),0,NULL,SUM(prev_open_cnt) OVER()))
) * 100
as ENI_MEASURE21,
SUM(curr_open_days_cnt) OVER()/DECODE(SUM(curr_open_cnt) OVER(),0,NULL,SUM(curr_open_cnt) OVER() ) as ENI_MEASURE22
,(
(
SUM(curr_open_days_cnt) OVER()/DECODE(SUM(curr_open_cnt) OVER(),0,NULL,SUM(curr_open_cnt) OVER() )
- SUM(prev_open_days_cnt) OVER()/DECODE(SUM(prev_open_cnt) OVER(),0,NULL,SUM(prev_open_cnt) OVER() )
)
/DECODE(SUM(prev_open_days_cnt) OVER()/DECODE(SUM(prev_open_cnt) OVER(),0,NULL,SUM(prev_open_cnt) OVER() )
,0
,NULL
,SUM(prev_open_days_cnt) OVER()/DECODE(SUM(prev_open_cnt) OVER(),0,NULL,SUM(prev_open_cnt) OVER() )
)
) * 100 as ENI_MEASURE23
,SUM( avg1_cnt ) OVER() as ENI_MEASURE25
,SUM( avg2_cnt ) OVER() as ENI_MEASURE26
,SUM( avg3_cnt ) OVER() as ENI_MEASURE27
,SUM( avg4_cnt ) OVER() as ENI_MEASURE28
,(CASE WHEN curr_open_cnt IS NULL OR curr_open_cnt = 0 THEN
NULL
ELSE
' || l_open_url || '
END ) as ENI_MEASURE36
,SUM(curr_past_open_days_cnt) OVER()/DECODE(SUM(curr_open_cnt) OVER(),0,NULL,SUM(curr_open_cnt) OVER() ) as ENI_MEASURE37
,SUM(prev_past_open_days_cnt) OVER()/DECODE(SUM(prev_open_cnt) OVER(),0,NULL,SUM(prev_open_cnt) OVER() ) as ENI_MEASURE38
from
(
SELECT vby.value
,vby.id
,'|| l_item_description || ' as ENI_MEASURE30
,SUM(
case
When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,(&BIS_CURRENT_ASOF_DATE)+1))) > &BIS_CURRENT_ASOF_DATE
Then pdo.cnt
Else 0
end
) curr_open_cnt,
SUM(
case
When pdo.creation_date <= &BIS_PREVIOUS_ASOF_DATE
AND pdo.need_by_date < &BIS_PREVIOUS_ASOF_DATE
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,(&BIS_PREVIOUS_ASOF_DATE)+1))) > &BIS_PREVIOUS_ASOF_DATE
Then pdo.cnt
Else 0
End
) prev_open_cnt,
SUM(
case
When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1))) > &BIS_CURRENT_ASOF_DATE
Then ((&BIS_CURRENT_ASOF_DATE-pdo.creation_date)*pdo.cnt)
Else 0
end
) curr_open_days_cnt,
SUM(
case
When pdo.creation_date <= &BIS_PREVIOUS_ASOF_DATE
AND pdo.need_by_date < &BIS_PREVIOUS_ASOF_DATE
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE + 1))) > &BIS_PREVIOUS_ASOF_DATE
Then ((&BIS_PREVIOUS_ASOF_DATE-pdo.creation_date)*pdo.cnt)
Else 0
End
) prev_open_days_cnt,
SUM(
case
When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE +1))) > &BIS_CURRENT_ASOF_DATE
Then ((&BIS_CURRENT_ASOF_DATE-pdo.need_by_date)*pdo.cnt)
Else 0
end
) curr_past_open_days_cnt,
SUM(
case
When pdo.creation_date <= &BIS_PREVIOUS_ASOF_DATE
AND pdo.need_by_date < &BIS_PREVIOUS_ASOF_DATE
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,&BIS_PREVIOUS_ASOF_DATE + 1))) > &BIS_PREVIOUS_ASOF_DATE
Then ((&BIS_PREVIOUS_ASOF_DATE-pdo.need_by_date)*pdo.cnt)
Else 0
End
) prev_past_open_days_cnt,
SUM(
case
When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE + 1))) > &BIS_CURRENT_ASOF_DATE
AND (&BIS_CURRENT_ASOF_DATE-pdo.need_by_date) between 0 and 1
Then pdo.cnt
Else 0
end
) avg1_cnt,
SUM(
case
When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE + 1))) > &BIS_CURRENT_ASOF_DATE
AND (&BIS_CURRENT_ASOF_DATE-pdo.need_by_date) between 2 and 5
Then pdo.cnt
Else 0
end
) avg2_cnt,
SUM(
case
When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE + 1))) > &BIS_CURRENT_ASOF_DATE
AND (&BIS_CURRENT_ASOF_DATE-pdo.need_by_date) between 6 and 10
Then pdo.cnt
Else 0
end
) avg3_cnt,
SUM(
case
When pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
AND pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,&BIS_CURRENT_ASOF_DATE + 1))) > &BIS_CURRENT_ASOF_DATE
AND (&BIS_CURRENT_ASOF_DATE-pdo.need_by_date) > 10
Then pdo.cnt
Else 0
end
) avg4_cnt
FROM
eni_dbi_co_dnum_mv pdo,' ||
l_viewby_tbl || '
WHERE
pdo.need_by_date is not null
and pdo.need_by_date < &BIS_CURRENT_ASOF_DATE
and nvl(pdo.implementation_date, &BIS_CURRENT_ASOF_DATE + 1) > pdo.need_by_date
and nvl(pdo.cancellation_date, &BIS_CURRENT_ASOF_DATE + 1) > pdo.need_by_date ' ||
l_join_col_name ||
l_item_where ||
l_priority_where ||
l_type_where ||
l_reason_where ||
l_status_where ||
l_org_where || '
GROUP BY
vby.value,
vby.id'
|| l_item_desc_grp || '
)t
WHERE
curr_open_cnt <> 0 or prev_open_cnt <> 0
GROUP BY
value,
id,
ENI_MEASURE30,
curr_open_cnt,
prev_open_cnt,
curr_open_days_cnt,
prev_open_days_cnt,
curr_past_open_days_cnt,
prev_past_open_days_cnt,
avg1_cnt,
avg2_cnt,
avg3_cnt,
avg4_cnt
ORDER BY
' || l_order_by;