[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NULL AS VIEWBY,
NULL AS ENI_MEASURE1,
NULL AS ENI_MEASURE9,
NULL AS ENI_MEASURE10,
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_MEASURE41,
NULL AS ENI_MEASURE42,
NULL AS ENI_MEASURE43,
NULL AS ENI_MEASURE44,
NULL AS ENI_MEASURE36
FROM DUAL';
select t.name as VIEWBY
,curr_open_cnt as ENI_MEASURE1
,prev_open_cnt as ENI_MEASURE9
,NVL(curr_open_cnt,0) as ENI_MEASURE10
,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
,NULL AS ENI_MEASURE41
,NULL AS ENI_MEASURE42
,NULL AS ENI_MEASURE43
,NULL AS ENI_MEASURE44
,NULL as ENI_MEASURE36
from
(
SELECT t.name,
t.start_date,
t.c_end_date,
SUM(
case
When pdo.creation_date <= t.c_end_date
AND pdo.need_by_date < t.c_end_date
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
Then pdo.cnt
Else NULL
end
) curr_open_cnt,
SUM(
case
When pdo.creation_date <= t.p_end_date
AND pdo.need_by_date < t.p_end_date
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,t.p_end_date+1))) > t.p_end_date
Then pdo.cnt
Else NULL
End
) prev_open_cnt,
SUM(
case
When pdo.creation_date <= t.c_end_date
AND pdo.need_by_date < t.c_end_date
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
Then ((t.c_end_date - pdo.creation_date)*pdo.cnt)
Else NULL
end
) curr_open_days_cnt,
SUM(
case
When pdo.creation_date <= t.p_end_date
AND pdo.need_by_date < t.p_end_date
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,t.p_end_date+1))) > t.p_end_date
Then ((t.p_end_date-pdo.creation_date)*pdo.cnt)
Else NULL
End
) prev_open_days_cnt,
SUM(
case
When pdo.creation_date <= t.c_end_date
AND pdo.need_by_date < t.c_end_date
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
Then ((t.c_end_date -pdo.need_by_date)*pdo.cnt)
Else NULL
end
) curr_past_open_days_cnt,
SUM(
case
When pdo.creation_date <= t.p_end_date
AND pdo.need_by_date < t.p_end_date
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,t.p_end_date+1))) > t.p_end_date
Then ((t.p_end_date-pdo.need_by_date)*pdo.cnt)
Else NULL
End
) prev_past_open_days_cnt,
SUM(
case
When pdo.creation_date <= t.c_end_date
AND pdo.need_by_date < t.c_end_date
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
AND (t.c_end_date-pdo.need_by_date) between 0 and 1
Then pdo.cnt
Else NULL
end
) avg1_cnt,
SUM(
case
When pdo.creation_date <= t.c_end_date
AND pdo.need_by_date < t.c_end_date
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
AND (t.c_end_date-pdo.need_by_date) between 2 and 5
Then pdo.cnt
Else NULL
end
) avg2_cnt,
SUM(
case
When pdo.creation_date <= t.c_end_date
AND pdo.need_by_date < t.c_end_date
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
AND (t.c_end_date-pdo.need_by_date) between 6 and 10
Then pdo.cnt
Else NULL
end
) avg3_cnt,
SUM(
case
When pdo.creation_date <= t.c_end_date
AND pdo.need_by_date < t.c_end_date
AND (NVL(IMPLEMENTATION_DATE,
NVL(CANCELLATION_DATE,t.c_end_date+1))) > t.c_end_date
AND (t.c_end_date-pdo.need_by_date) > 10
Then pdo.cnt
Else NULL
end
) avg4_cnt
FROM
eni_dbi_co_dnum_mv pdo,' ||
l_from_clause || '
WHERE
pdo.creation_date <= &BIS_CURRENT_ASOF_DATE
AND pdo.need_by_date is not null' ||
l_item_where ||
l_priority_where ||
l_type_where ||
l_reason_where ||
l_status_where ||
l_org_where || '
GROUP BY
' || l_group_by_clause || '
)ftrs,' || l_from_clause || '
WHERE
1 = 1
and t.name = ftrs.name(+)
ORDER BY t.start_date' || l_order;