Archives of the TeradataForum

Message Posted: Thu, 03 Jun 2004 @ 13:08:56 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello Everybody,

We are trying to create this view,

in which FP_CARR_INV ----- INV_ID (Primary index) and SBU_ID (secondary index)
FP_CARR_PYMT ------------- INV_ID (Primary index) and SBU_ID (secondary index)
FP_SHP_ORD_LN_REORG ------ MSTR_BOL ,MATL_ID  (Primary index)

are the base views as we don't have access to use the tables directly,

Teradata is taking almost 1 hr 20 mins in creating this view please suggest me some good way of increasing the performance.

Select A.SBU_ID,
        A.INV_ID,
        B.LCL_CRNCY,
        B.PAID_LCL As DLVRY_PAID_LCL,
        B.PAID_USD As DLVRY_PAID_USD,
        (CASE WHEN B.BOL_STAT_CD < 70 THEN A.RATED_LCL
                WHEN B.BOL_STAT_CD = 70 THEN A.APPRVD_LCL
                WHEN B.BOL_STAT_CD = 80 AND B.PAID_LCL <> 0.01
                        THEN B.PAID_LCL
                WHEN B.BOL_STAT_CD = 80 AND B.PAID_LCL = 0.01
                        THEN A.APPRVD_LCL ELSE 0.0 END) As DLVRY_COST_LCL,
        (CASE WHEN B.BOL_STAT_CD < 70 THEN A.RATED_USD
                WHEN B.BOL_STAT_CD = 70 THEN A.APPRVD_USD
                WHEN B.BOL_STAT_CD = 80 AND B.PAID_USD <> 0.01
                        THEN B.PAID_USD
                WHEN B.BOL_STAT_CD = 80 AND B.PAID_USD = 0.01
                        THEN A.APPRVD_USD ELSE 0.0 END) As DLVRY_COST_USD,
        B.DAYS_PD_AFTR_SHIP As DAYS_PD_AFTR_SHIP,
        B.DAYS_PD_AFTR_DLVR As DAYS_PD_AFTR_DLVR
From (Select
        FP_CARR_INV.SBU_ID,
        FP_CARR_INV.INV_ID,
        FP_CARR_INV.BOL_STAT_CD,
        FP_CARR_PYMT.CRNCY_ID As LCL_CRNCY,
        (CASE WHEN FP_CARR_PYMT.AP_PAY_INV_LCL_AMT IS NULL THEN 0.0
                WHEN FP_CARR_PYMT.AP_PAY_INV_LCL_AMT=0.0
                        THEN 0.01 ELSE FP_CARR_PYMT.AP_PAY_INV_LCL_AMT END) As PAID_LCL,
        (CASE WHEN FP_CARR_PYMT.AP_PAY_INV_USD_AMT IS NULL THEN 0.0
                WHEN FP_CARR_PYMT.AP_PAY_INV_USD_AMT=0.0
                        THEN 0.01 ELSE FP_CARR_PYMT.AP_PAY_INV_USD_AMT END) As PAID_USD,
        (CASE WHEN FP_CARR_PYMT.AP_PAY_DT IS NULL
                        THEN 0 ELSE (FP_CARR_PYMT.AP_PAY_DT - FP_CARR_INV.SHIP_DT ) END) As DAYS_PD_AFTR_SHIP,
        (CASE WHEN FP_CARR_PYMT.AP_PAY_DT IS NULL
                        THEN 0 ELSE (FP_CARR_PYMT.AP_PAY_DT - FP_CARR_INV.INV_DT ) END) As DAYS_PD_AFTR_DLVR

From
        dev_vws.FP_CARR_INV as FP_CARR_INV Left Outer Join
        dev_vws.FP_CARR_PYMT as FP_CARR_PYMT on
        FP_CARR_INV.INV_ID = FP_CARR_PYMT.INV_ID and
        FP_CARR_INV.SBU_ID = FP_CARR_PYMT.SBU_ID
Where  Current_date between FP_CARR_INV.EFF_DT and FP_CARR_INV.EXP_DT
        and Current_date between FP_CARR_PYMT.EFF_DT and FP_CARR_PYMT.EXP_DT) B,
( Select         FP_SHP_ORD_LN_CST_REORG.SBU_ID,
        FP_SHP_ORD_LN_CST_REORG.INV_ID,
        SUM(FP_SHP_ORD_LN_CST_REORG.RATED_LCL) As RATED_LCL,
        SUM(FP_SHP_ORD_LN_CST_REORG.RATED_USD) As RATED_USD,
        SUM(FP_SHP_ORD_LN_CST_REORG.APPRVD_LCL) As APPRVD_LCL,
        SUM(FP_SHP_ORD_LN_CST_REORG.APPRVD_USD) As APPRVD_USD
From
gdyr_bi_vws.FP_SHP_ORD_LN_CST_REORG As FP_SHP_ORD_LN_CST_REORG
where FP_SHP_ORD_LN_CST_REORG.BUS_DT = Current_Date
Group by 1,2
) A
Where A.SBU_ID = B.SBU_ID and A.INV_ID = B.INV_ID

Shilpa Ramlal Bateja

	<Prev	Next>		<<First	<Prev	Next>	Last>>