General SQL Parser is a Java/.NET library. It provides a rich set of APIs to parse, decode, analyze and rewrite SQL scripts. Supports more than 10 major database platforms. This repo provides demos and testcases to help people make better use of the General SQL Parser
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 )
;
https://github.com/sqlparser/gsp_demo/tree/master/java/src/demos/analyzeview This demo can't work correctly using the latest gsp java core library for this Teradata script:
Original related issue : https://github.com/sqlparser/wings/issues/166