sql query to ETL
Posted: Tue Dec 07, 2004 12:50 pm
Hi,
Client gave a requirment like this they wanna it do with ETL now
i have few quesions if u can help as they are using these 3 source table
FROM clause , my question is how can i proceed in ETL job to get this query .
Thanks
Bobby
INSERT INTO
dwadmin.FCT_REVENUE_SUMMARY_JAN_2004
(SELECT MAX(F.REVENUE_KEY),
DECODE(F.REVENUE_TYPE_KEY, 10000003, TRUNC(F.BILL_END_DT, 'MONTH'), TRUNC(POSTED_DT, 'MONTH')),
F.SUBSCRIBER_KEY,
F.SUBSCRIBER_DIM_KEY,
TRUNC(F.CYCLE_DT, 'MONTH'),
TRUNC(F.BILL_START_DT, 'MONTH'),
TRUNC(F.BILL_END_DT, 'MONTH'),
TRUNC(F.TRAN_DT, 'MONTH'),
TRUNC(F.POSTED_DT, 'MONTH'),
DECODE(SIGN(F.PPV_KEY), 1, TRUNC(F.POSTED_DT, 'MONTH'), TRUNC(F.BILL_END_DT, 'MONTH')),
DECODE(SIGN(F.PPV_KEY), 1, TRUNC(F.TRAN_DT, 'MONTH'), NULL),
F.PROGRAM_PACKAGE_DIM_KEY,
F.PROGRAM_PACKAGE_KEY,
F.SERVICE_DIM_KEY,
F.SERVICE_KEY,
DECODE(SIGN(F.PROGRAM_PACKAGE_KEY), 1, 'P',
DECODE(SIGN(F.PPV_KEY), 1, 'V',
DECODE(SIGN(F.SERVICE_KEY), 1, 'S', 'X'))),
DECODE(SIGN(F.PROGRAM_PACKAGE_KEY), 1, 'P' || F.PROGRAM_PACKAGE_KEY,
DECODE(SIGN(F.PPV_KEY), 1, 'V' || F.PPV_KEY,
DECODE(SIGN(F.SERVICE_KEY), 1, 'S' || F.SERVICE_KEY, NULL))),
F.PPV_KEY,
F.REVENUE_TYPE_KEY,
F.PARTIAL_BILLING_FLG,
SUM(F.BILLING_AMT),
F.SOURCE_OF_DATA,
S.REGION_KEY,
S.SUBSCRIBER_TYPE_KEY,
S.SUBSCRIBER_LANGUAGE_KEY,
S.VIP_KEY,
S.SUBSCRIBER_STATUS_KEY,
S.RURAL_URBAN_FLG,
S.PROMOTION_CAMP_KEY,
TRUNC(S.CREATE_DATE, 'MONTH'),
TRUNC(S.DISCONNECT_DT, 'MONTH'),
S.DISCONNECT_REASON_CD,
S.NO_OF_MTH_BILL_PERIOD,
S.DISCOUNT_CD,
TRUNC(SB.BILL_END_DT, 'MONTH'),
SB.SERVICE_DIM_KEY,
SB.SERVICE_KEY,
SB.A_LA_CARTE_LEVEL,
SUM(SB.BILLING_AMT),
SB.PROGRAM_PACKAGE_KEY,
SB.PROGRAM_PACKAGE_DIM_KEY,
SB.SUBS_PRODUCT,
0,
TRUNC(S.CONNECT_DT, 'MONTH') CONNECT_DT
FROM DWADMIN.FCT_ALL_REVENUE F,
DWADMIN.DIM_SUBSCRIBER S,
DWADMIN.DIM_SUBSCRIBER_BASIC SB
WHERE F.SUBSCRIBER_DIM_KEY = S.SUBSCRIBER_DIM_KEY AND
F.REVENUE_KEY = SB.REVENUE_KEY(+) AND
TO_CHAR(TRUNC(DECODE(F.REVENUE_TYPE_KEY, 10000003, F.BILL_END_DT, POSTED_DT), 'MONTH'),'YYYY-MM-DD HH24:MI:SS') = '2004-01-01 00:00:00'
GROUP BY DECODE(F.REVENUE_TYPE_KEY, 10000003, TRUNC(F.BILL_END_DT, 'MONTH'), TRUNC(POSTED_DT, 'MONTH')),
F.SUBSCRIBER_KEY,
F.SUBSCRIBER_DIM_KEY,
TRUNC(F.CYCLE_DT, 'MONTH'),
TRUNC(F.BILL_START_DT, 'MONTH'),
TRUNC(F.BILL_END_DT, 'MONTH'),
TRUNC(F.TRAN_DT, 'MONTH'),
TRUNC(F.POSTED_DT, 'MONTH'),
DECODE(SIGN(F.PPV_KEY), 1, TRUNC(F.POSTED_DT, 'MONTH'), TRUNC(F.BILL_END_DT, 'MONTH')),
DECODE(SIGN(F.PPV_KEY), 1, TRUNC(F.TRAN_DT, 'MONTH'), NULL),
F.PROGRAM_PACKAGE_DIM_KEY,
F.PROGRAM_PACKAGE_KEY,
F.SERVICE_DIM_KEY,
F.SERVICE_KEY,
DECODE(SIGN(F.PROGRAM_PACKAGE_KEY), 1, 'P',
DECODE(SIGN(F.PPV_KEY), 1, 'V',
DECODE(SIGN(F.SERVICE_KEY), 1, 'S', 'X'))),
DECODE(SIGN(F.PROGRAM_PACKAGE_KEY), 1, 'P' || F.PROGRAM_PACKAGE_KEY,
DECODE(SIGN(F.PPV_KEY), 1, 'V' || F.PPV_KEY,
DECODE(SIGN(F.SERVICE_KEY), 1, 'S' || F.SERVICE_KEY, NULL))),
F.PPV_KEY,
F.REVENUE_TYPE_KEY,
F.PARTIAL_BILLING_FLG,
F.SOURCE_OF_DATA,
S.REGION_KEY,
S.SUBSCRIBER_TYPE_KEY,
S.SUBSCRIBER_LANGUAGE_KEY,
S.VIP_KEY,
S.SUBSCRIBER_STATUS_KEY,
S.RURAL_URBAN_FLG,
S.PROMOTION_CAMP_KEY,
TRUNC(S.CREATE_DATE, 'MONTH'),
TRUNC(S.DISCONNECT_DT, 'MONTH'),
S.DISCONNECT_REASON_CD,
S.NO_OF_MTH_BILL_PERIOD,
S.DISCOUNT_CD,
TRUNC(SB.BILL_END_DT, 'MONTH'),
SB.SERVICE_DIM_KEY,
SB.SERVICE_KEY,
SB.A_LA_CARTE_LEVEL,
SB.PROGRAM_PACKAGE_KEY,
SB.PROGRAM_PACKAGE_DIM_KEY,
SB.SUBS_PRODUCT,
0,
TRUNC(S.CONNECT_DT, 'MONTH'));
commit;
update FCT_REVENUE_SUMMARY_JAN_2004
set subscriber_ct = 1
where revenue_key
in (
select max(revenue_key)
from FCT_REVENUE_SUMMARY_JAN_2004
group by
subscriber_key);
commit;
Client gave a requirment like this they wanna it do with ETL now
i have few quesions if u can help as they are using these 3 source table
FROM clause , my question is how can i proceed in ETL job to get this query .
Thanks
Bobby
INSERT INTO
dwadmin.FCT_REVENUE_SUMMARY_JAN_2004
(SELECT MAX(F.REVENUE_KEY),
DECODE(F.REVENUE_TYPE_KEY, 10000003, TRUNC(F.BILL_END_DT, 'MONTH'), TRUNC(POSTED_DT, 'MONTH')),
F.SUBSCRIBER_KEY,
F.SUBSCRIBER_DIM_KEY,
TRUNC(F.CYCLE_DT, 'MONTH'),
TRUNC(F.BILL_START_DT, 'MONTH'),
TRUNC(F.BILL_END_DT, 'MONTH'),
TRUNC(F.TRAN_DT, 'MONTH'),
TRUNC(F.POSTED_DT, 'MONTH'),
DECODE(SIGN(F.PPV_KEY), 1, TRUNC(F.POSTED_DT, 'MONTH'), TRUNC(F.BILL_END_DT, 'MONTH')),
DECODE(SIGN(F.PPV_KEY), 1, TRUNC(F.TRAN_DT, 'MONTH'), NULL),
F.PROGRAM_PACKAGE_DIM_KEY,
F.PROGRAM_PACKAGE_KEY,
F.SERVICE_DIM_KEY,
F.SERVICE_KEY,
DECODE(SIGN(F.PROGRAM_PACKAGE_KEY), 1, 'P',
DECODE(SIGN(F.PPV_KEY), 1, 'V',
DECODE(SIGN(F.SERVICE_KEY), 1, 'S', 'X'))),
DECODE(SIGN(F.PROGRAM_PACKAGE_KEY), 1, 'P' || F.PROGRAM_PACKAGE_KEY,
DECODE(SIGN(F.PPV_KEY), 1, 'V' || F.PPV_KEY,
DECODE(SIGN(F.SERVICE_KEY), 1, 'S' || F.SERVICE_KEY, NULL))),
F.PPV_KEY,
F.REVENUE_TYPE_KEY,
F.PARTIAL_BILLING_FLG,
SUM(F.BILLING_AMT),
F.SOURCE_OF_DATA,
S.REGION_KEY,
S.SUBSCRIBER_TYPE_KEY,
S.SUBSCRIBER_LANGUAGE_KEY,
S.VIP_KEY,
S.SUBSCRIBER_STATUS_KEY,
S.RURAL_URBAN_FLG,
S.PROMOTION_CAMP_KEY,
TRUNC(S.CREATE_DATE, 'MONTH'),
TRUNC(S.DISCONNECT_DT, 'MONTH'),
S.DISCONNECT_REASON_CD,
S.NO_OF_MTH_BILL_PERIOD,
S.DISCOUNT_CD,
TRUNC(SB.BILL_END_DT, 'MONTH'),
SB.SERVICE_DIM_KEY,
SB.SERVICE_KEY,
SB.A_LA_CARTE_LEVEL,
SUM(SB.BILLING_AMT),
SB.PROGRAM_PACKAGE_KEY,
SB.PROGRAM_PACKAGE_DIM_KEY,
SB.SUBS_PRODUCT,
0,
TRUNC(S.CONNECT_DT, 'MONTH') CONNECT_DT
FROM DWADMIN.FCT_ALL_REVENUE F,
DWADMIN.DIM_SUBSCRIBER S,
DWADMIN.DIM_SUBSCRIBER_BASIC SB
WHERE F.SUBSCRIBER_DIM_KEY = S.SUBSCRIBER_DIM_KEY AND
F.REVENUE_KEY = SB.REVENUE_KEY(+) AND
TO_CHAR(TRUNC(DECODE(F.REVENUE_TYPE_KEY, 10000003, F.BILL_END_DT, POSTED_DT), 'MONTH'),'YYYY-MM-DD HH24:MI:SS') = '2004-01-01 00:00:00'
GROUP BY DECODE(F.REVENUE_TYPE_KEY, 10000003, TRUNC(F.BILL_END_DT, 'MONTH'), TRUNC(POSTED_DT, 'MONTH')),
F.SUBSCRIBER_KEY,
F.SUBSCRIBER_DIM_KEY,
TRUNC(F.CYCLE_DT, 'MONTH'),
TRUNC(F.BILL_START_DT, 'MONTH'),
TRUNC(F.BILL_END_DT, 'MONTH'),
TRUNC(F.TRAN_DT, 'MONTH'),
TRUNC(F.POSTED_DT, 'MONTH'),
DECODE(SIGN(F.PPV_KEY), 1, TRUNC(F.POSTED_DT, 'MONTH'), TRUNC(F.BILL_END_DT, 'MONTH')),
DECODE(SIGN(F.PPV_KEY), 1, TRUNC(F.TRAN_DT, 'MONTH'), NULL),
F.PROGRAM_PACKAGE_DIM_KEY,
F.PROGRAM_PACKAGE_KEY,
F.SERVICE_DIM_KEY,
F.SERVICE_KEY,
DECODE(SIGN(F.PROGRAM_PACKAGE_KEY), 1, 'P',
DECODE(SIGN(F.PPV_KEY), 1, 'V',
DECODE(SIGN(F.SERVICE_KEY), 1, 'S', 'X'))),
DECODE(SIGN(F.PROGRAM_PACKAGE_KEY), 1, 'P' || F.PROGRAM_PACKAGE_KEY,
DECODE(SIGN(F.PPV_KEY), 1, 'V' || F.PPV_KEY,
DECODE(SIGN(F.SERVICE_KEY), 1, 'S' || F.SERVICE_KEY, NULL))),
F.PPV_KEY,
F.REVENUE_TYPE_KEY,
F.PARTIAL_BILLING_FLG,
F.SOURCE_OF_DATA,
S.REGION_KEY,
S.SUBSCRIBER_TYPE_KEY,
S.SUBSCRIBER_LANGUAGE_KEY,
S.VIP_KEY,
S.SUBSCRIBER_STATUS_KEY,
S.RURAL_URBAN_FLG,
S.PROMOTION_CAMP_KEY,
TRUNC(S.CREATE_DATE, 'MONTH'),
TRUNC(S.DISCONNECT_DT, 'MONTH'),
S.DISCONNECT_REASON_CD,
S.NO_OF_MTH_BILL_PERIOD,
S.DISCOUNT_CD,
TRUNC(SB.BILL_END_DT, 'MONTH'),
SB.SERVICE_DIM_KEY,
SB.SERVICE_KEY,
SB.A_LA_CARTE_LEVEL,
SB.PROGRAM_PACKAGE_KEY,
SB.PROGRAM_PACKAGE_DIM_KEY,
SB.SUBS_PRODUCT,
0,
TRUNC(S.CONNECT_DT, 'MONTH'));
commit;
update FCT_REVENUE_SUMMARY_JAN_2004
set subscriber_ct = 1
where revenue_key
in (
select max(revenue_key)
from FCT_REVENUE_SUMMARY_JAN_2004
group by
subscriber_key);
commit;