sqlparser / gsp_demo Goto Github PK

CREATE VIEW LADEHISTORIE_V

AS

SELECT

objekt, datum, endgueltigflag, zeitstempel

FROM

(

            SELECT

                          'Programm' AS objekt

                        , r.datum

                        , MAX(s.endgueltigflag) AS endgueltigflag

                        , MAX(r.load_timestamp) AS zeitstempel

            FROM dm_mediafacts.reichweite_v r

            JOIN dm_mediafacts.sendeprotokoll_v s

            ON r.p_hk = s.p_hk

            GROUP BY 1, 2

            UNION

            SELECT

                          'Tagesmarktanteil' AS objekt

                        , datum

                        , MAX(endgueltigflag) AS endgueltigflag

                        , MAX(load_timestamp) AS zeitstempel

            FROM dm_mediafacts.ganztag_v

            GROUP BY 1, 2

            UNION

            SELECT

                          '5-Minuten' AS objekt

                        , datum

                        , MAX(endgueltigflag) AS endgueltigflag

                        , MAX(load_timestamp) AS zeitstempel

            FROM dm_mediafacts.intervall5_v

            GROUP BY 1, 2

) t
;
 

CREATE VIEW ganztag_v

AS

SELECT

    CAST(s_tg.videoscope_date AS DATE) AS DATUM

    , s_mf.sender_id       AS SENDER_ID

    , s_zg.zielgruppe_id   AS ZIELGRUPPE_ID

    , s_tg.sehbeteiligung_mio AS SEHB_MIO

    , s_tg.sehbeteiligung_tvgesamt_mio AS SEHB_FSGES_MIO

    , s_tg.marktanteil     AS MARKTANTEIL

    , s_stis.ist_endgueltig AS ENDGUELTIGFLAG

  , GREATEST(

      s_tg.load_timestamp

    , s_mf.load_timestamp

    , s_zg.load_timestamp

    ) AS LOAD_TIMESTAMP

FROM raw_vault.s_slotmessungen_tagesslot s_tg

JOIN raw_vault.l_slotmessungen_sender l_sms

  ON s_tg.h_slotmessungen_hk = l_sms.h_slotmessungen_hk

JOIN raw_vault.l_zielgruppe_slotmessungen l_zs

  ON s_tg.h_slotmessungen_hk = l_zs.h_slotmessungen_hk

JOIN business_vault.s_slotmessungen_tagesslot_bv_ist_endgueltig s_stis

  ON s_tg.h_slotmessungen_hk = s_stis.h_slotmessungen_hk

JOIN raw_vault.s_sender_mf s_mf

  ON l_sms.h_sender_hk = s_mf.h_sender_hk

AND s_mf.is_deleted = FALSE

AND s_mf.delivery_end_timestamp = '9999-12-31 00:00:00.0'

JOIN raw_vault.s_zielgruppe_mf s_zg

  ON l_zs.h_zielgruppe_hk = s_zg.h_zielgruppe_hk

AND s_zg.is_deleted = FALSE

AND s_zg.delivery_end_timestamp = '9999-12-31 00:00:00.0'

WHERE s_tg.is_deleted = FALSE

AND s_tg.delivery_end_timestamp = '9999-12-31 00:00:00.0'

replace	view APBV.Complex_View as 
SELECT 
 BSUBS.edw_key AS Subs_Id, 
 BACCT.edw_key AS Acct_Id, 
 BCUST.edw_key AS Cust_Id, 
 fact.srvc_key, 
 prov_prepay.Recharge_Name,
 wbsd.prd_type_cd, 
 Substr(CASE 
 		WHEN acc.account_type = 'POST' THEN 'Postpay' 
 		WHEN acc.account_type = 'PRE' THEN 'Prepay' 
 		ELSE acc.account_type 
 		END, 1, 30) AS Acct_Type_Cd, 
 CASE 
 	WHEN acc.account_type = 'POST' THEN 
 		CASE 
 			WHEN crp.plan_grp2_code = 'Voice' THEN 'Handset' 
 			ELSE 
 				COALESCE(COALESCE(crp.plan_grp2_code, BSM1.plan_type_cd), 'Handset') 
 			END 
 		ELSE 
 			CASE 
 				WHEN ht.product_curr_name = 'USIM Only Mobile Broadband' 
 					OR ht.data_capablty_id = 'MBB' THEN 'MBB' 
 				WHEN prov_prepay.line_of_bus = 'Voice' THEN 'Handset' 
 				ELSE COALESCE(COALESCE(prov_prepay.line_of_bus, BSM1.plan_type_cd), 'Handset') 
 			END 
 		END AS Plan_Type_Cd,  
 370 AS Ctl_Id, 
 
FROM APBV.h_snap_cdw AS fact 
 INNER JOIN APBV.w_bus_snap_drvr AS wbsd 
 	ON ( fact.prd_dt = wbsd.prd_dt 
 			AND wbsd.subj_area_cd = fact.subj_area_cd 
 			AND wbsd.prd_type_cd IN ( 'WEEK', 'MONTH' ) ) 
 INNER JOIN accv.cdw_ser_service AS svc 
 	ON ( fact.srvc_key = svc.service_key ) 
 INNER JOIN accv.cdw_acc_account AS acc 
 	ON ( svc.account_id = acc.account_id 
 			AND fact.prd_dt || ' 23:59:59' BETWEEN acc.eff_start_date AND acc.eff_end_date 
 		) 
 INNER JOIN accv.cdw_cus_customer AS cus 
 	ON ( acc.customer_id = cus.customer_id 
 			AND fact.prd_dt || ' 23:59:59' BETWEEN cus.eff_start_date AND  cus.eff_end_date 
 		) 
 INNER JOIN APBV.bkey_subs_id AS BSUBS 
 	ON ( svc.service_instld_product_id = BSUBS.source_key 
 			AND BSUBS.end_dt = '9999-12-31' ) 
 INNER JOIN APBV.bkey_acct_id AS BACCT 
 	ON ( acc.account_id = BACCT.source_key 
 		AND BACCT.end_dt = '9999-12-31' ) 
 INNER JOIN APBV.bkey_cust_id AS BCUST 
 	ON ( cus.customer_id = BCUST.source_key 
 			AND BCUST.end_dt = '9999-12-31' ) 
 INNER JOIN APBV.w_cdw_dim_snap_drvr AS Dt_Fact 
 	ON ( fact.srvc_key = Dt_Fact.srvc_key 
 			AND fact.meas_cd = Dt_Fact.meas_cd 
 			AND fact.prd_dt = Dt_Fact.prd_dt ) 
 LEFT JOIN accv.cdw_cus_customer_indiv AS cusind 
 	ON ( cusind.customer_id = cus.customer_id 
 		AND fact.prd_dt || ' 23:59:59' BETWEEN cusind.eff_start_date AND  cusind.eff_end_date ) 
 LEFT JOIN accv.cdw_cus_customer_org AS cusorg 
 	ON ( cus.customer_id = cusorg.customer_id 
 		AND fact.prd_dt || ' 23:59:59' BETWEEN cusorg.eff_start_date AND  cusorg.eff_end_date ) 
 LEFT JOIN accv.cdw_mrd_rate_plan AS crp 
 	ON ( crp.product_id = Dt_Fact.sptp_product_id 
 		AND crp.curr_flag = 'Y' ) 
 LEFT JOIN accv.cdw_ser_order_events AS oe 
 	ON ( oe.event_capture_id = svc.sale_order_capture_id 
 		AND oe.order_stat <> '1' ) 
 LEFT JOIN amv.prod AS PROD_TP 
 	ON ( Dt_Fact.sptp_product_id = PROD_TP.prod_cd ) 
 LEFT JOIN amv.prod AS Prod_HS 
 	ON ( Dt_Fact.sphs_product_id = Prod_HS.prod_cd ) 
 LEFT JOIN accv.cdw_mrd_handset_type AS ht 
 	ON ( Dt_Fact.sphs_product_id = ht.product_id 
 		AND ht.curr_flag = 'Y' ) 
 LEFT JOIN (SELECT DISTINCT CSRD.account_name, 
 	CSRD.service_name, 
 	CASE 
 		WHEN CSRD.payment_type LIKE 'Data Recharge%' THEN  'Data Recharge '  || CSRD.payment_amount 
 		ELSE 'Voice Recharge ' || CSRD.payment_amount 
 	END AS Recharge_Name, 
 Rank() 
 	OVER(partition BY CSRD.account_name, 
 				CSRD.service_name 
 			ORDER BY CSRD.receipt_nr ) AS dr 
 FROM accv.cdw_svw_recharge_dtls AS CSRD 
 LEFT JOIN accv.cdw_mrd_rate_plan AS 
 prepay_plan 
 ON ( CSRD.payment_amount = 
 prepay_plan.mthly_cap_amt 
 AND prepay_plan.curr_flag = 'Y' 
 AND prepay_plan.rate_plan_ldesc LIKE 
 '%Prepaid%' 
 AND prepay_plan.plan_grp2_code = 'Voice' ) 
 LEFT JOIN accv.cdw_mrd_rate_plan AS 
 prepay_plan_mbb 
 ON ( CSRD.payment_amount = 
 prepay_plan_mbb.mthly_cap_amt 
 AND prepay_plan_mbb.curr_flag = 'Y' 
 AND prepay_plan_mbb.rate_plan_ldesc LIKE 
 '%Prepaid%' 
 AND prepay_plan_mbb.plan_grp2_code = 'MBB' 
 )  
  WHERE CSRD.payment_type NOT IN ( 
 'Goodwill Adj', 'Prepaid Goodwill Adj', 
 'Provisioning failures Adj', 
 'Top-Up Correction Adjustment' )) AS 
 prov_prepay 
 ON ( Cast(prov_prepay.account_name AS DECIMAL(18, 0)) = 
 acc.account_num 
 AND prov_prepay.service_name = svc.service_num_code 
 AND prov_prepay.recharge_date = svc.service_actvn_date 
 AND prov_prepay.dr = 1 ) 
 
;

BEGIN 
SELECT * FROM test123.TableOK; CALL fnc.copy_table('test234.test1','test567test2'); CALL fnc.copy_table('test345.test3','test678.test4'); SELECT * FROM test456.test5 a INNER JOIN test789.test6 b ON a.rowid = b.rowid; 
END