December 14, 2009 at 11:57 am
I have to run this code. This code is already written in a data flow task in SSIS package. I ran this code but got errors and unable to find what error is and the highlighted code
Can anyone explain.
Select
shp_dt_key_no = dbo.awf00220_ord_fact_shp_dt(HDR.CNTRY, HDR.DLVR_DT, HDR.LAST_SHP_DT), ord_dt_key_no = dbo.awf00020_webm_dt_convert(HDR.ORD_DT),
ord_inv_dt_key_no = dbo.awf00020_webm_dt_convert(HDR.INV_DT),
pay_cfirm_dt_key_no = dbo.awf00020_webm_dt_convert(HDR.ORD_CFIRM_DT),
curcy_cd = dbo.awf00150_promo_curcy_cd(HDR.CNTRY, HDR.ORD_CURCY, HDR.ORD_CURCY),
bns_mo_yr_key_no = dbo.awf00025_webm_yr_mo_convert(HDR.BNS_PER),
ord_cntry_key_no = dbo.awf00530_OMS_CNTRY_KEY_NO(case when len(isnull(HDR.INTGRT_CNTRY_CD,''))=0 then HDR.CNTRY
else HDR.CNTRY end, ord_cntry_lkp.cntry_key_no,HDR.ORD_CURCY),
ord_imc_no = HDR.ORD_DISTB,
vol_imc_no = HDR.VOL_DISTB,
shp_imc_no = HDR.SHP_DISTB,
ord_cd_key_no = ord_cd_lkp.ord_cd_key_no,
whse_cd = HDR.ORD_WHSE,
ord_no = HDR.ORD,
orgnl_ord_no = HDR.ORGNL_ORD,
ref_ord_no = IsNull(HDR.REF_ORD, -1),
cntry_cd = HDR.cntry,
aff_id = cntry_aff_lkp.aff_id,
ord_shp_flg = HDR.ORD_SHP,
tax_lc_amt = HDR.ORD_LVL_TAX
From --dbo.DWT30023_ORD_HDR_GOLD HDR
(Select
HDR.ORD, HDR.INTGRT_CNTRY_CD, HDR.INTGRT_SRC_TRX_DT, MAX(ROW_ID_NO) as MAX_ROW_ID_NO
From
(Select
HDR.ORD, HDR.INTGRT_CNTRY_CD, MAX(INTGRT_SRC_TRX_DT) as MAX_INTGRT_SRC_TRX_DT
From dbo.DWT30023_ORD_HDR HDR with (rowlock, index=DWT30023_SRC_TRX_DT_CNTRY_ORD)
Where ETL_PROC_STAT = 1
Group By HDR.ORD, HDR.INTGRT_CNTRY_CD
) HDR_WEBM
inner join dbo.DWT30023_ORD_HDR HDR (rowlock) on HDR.ORD = HDR_WEBM.ORD and HDR.INTGRT_CNTRY_CD = HDR_WEBM.INTGRT_CNTRY_CD and HDR.INTGRT_SRC_TRX_DT = HDR_WEBM.MAX_INTGRT_SRC_TRX_DT
Group By HDR.ORD, HDR.INTGRT_CNTRY_CD, HDR.INTGRT_SRC_TRX_DT
) HDR_GOLD
inner join dbo.DWT30023_ORD_HDR HDR (rowlock) on HDR.ROW_ID_NO = HDR_GOLD.MAX_ROW_ID_NO
inner join dbo.dwt20260_cntry_key_lkp cntry_lkp (nolock) on HDR.CNTRY=cntry_lkp.amway_cntry_cd
left outer join dbo.dwt20030_cntry_aff_lkp cntry_aff_lkp (nolock) on HDR.CNTRY=cntry_aff_lkp.amway_cntry_cd
left outer join dbo.dwt20230_aff_key_lkp aff_lkp (nolock) on cntry_aff_lkp.aff_id=aff_lkp.aff_id
left outer join dbo.dwt20260_cntry_key_lkp ord_cntry_lkp on case when len(isnull(HDR.INTGRT_CNTRY_CD,''))=0 then HDR.CNTRY else HDR.CNTRY end=ord_cntry_lkp.amway_cntry_cd
left outer join dbo.awt10070_ord_cd_key_lkp ord_cd_lkp
on dbo.awf00530_OMS_CNTRY_KEY_NO(case when len(isnull(HDR.INTGRT_CNTRY_CD,''))=0 then HDR.CNTRY else HDR.CNTRY end,
ord_cntry_lkp.cntry_key_no,HDR.ORD_CURCY)=ord_cd_lkp.cntry_key_no
and IsNull(HDR.ORD_CANC, ' ')=ord_cd_lkp.ord_canc_cd
and IsNull(HDR.RMV_HNDLG_FEE, ' ')=ord_cd_lkp.ord_free_shp_flg
and IsNull(HDR.ORD_SHP, ' ')=ord_cd_lkp.ord_shp_flg
and IsNull(HDR.ORD_TYPE, ' ')=ord_cd_lkp.ord_type_cd
and IsNull(dbo.awf00140_pay_bns_flg(HDR.CNTRY, HDR.BAS_UPDT, HDR.PAY_BNS), ' ')=ord_cd_lkp.pay_bns_flg
and IsNull(HDR.PAY_REQ, ' ')=ord_cd_lkp.pay_req_cd
and ' '=ord_cd_lkp.bo_rlse_cd
--Where ETL_PROC_STAT = 1
December 14, 2009 at 12:11 pm
Paste it into a management studio query window and do a syntax check. That should give you a message saying what's wrong and on what line.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2009 at 12:28 pm
I did as you said and i got it right.
But i didn't understand
shp_dt_key_no = dbo.awf00220_ord_fact_shp_dt(HDR.CNTRY, HDR.DLVR_DT, HDR.LAST_SHP_DT)
How can he throw 3 values into one
December 14, 2009 at 1:08 pm
Don't understand your question. It's a scalar function that takes 3 parameters and returns a value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2009 at 7:50 am
I got it
Thank you very much
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply