Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-09-11 : 06:05:25
|
Hey guys Hope all is well I need some help Table [FDMS].[dbo].[Dim_Outlet] has the correct MCC Code, however there are fdmsaccountno that dont have a MCC_code If there isnt a MCC_code in the [FDMS].[dbo].[Dim_Outlet] then i want to get the [FENT-MCC-CODE(4)] from table stg_LMPAB501 and populate the fdmsaccountno with that code So far i have got this --MCC Build Dim outlet-- SELECT [FDMSAccountNo],MCC_Codeinto #MCCDim_OutletFROM [FDMS].[dbo].[Dim_Outlet]--MCC Build 501-- SELECT stg_LMPAB501.FDMSAccountNo,[FENT-MCC-CODE(4)]into #MCC501FROM stg_LMPAB501 --Joining DimOutlet MCC & 501 Mcc tables together -- SELECT * into #MCCFROM #MCCDim_OutletUNIONSELECT *FROM #MCC501 But i have now hit a brick wall, would appreciate any help avaialble |
|
divya.ce
Starting Member
16 Posts |
Posted - 2013-09-11 : 06:54:58
|
Its very simple, just check for code present or not in main table, if not then take from stg table. If you want to bring all the codes from stg table which are not present at all in main table then you need to do full join, else if you want to bring Mcc_code where null in main table you can do left join to stg table on FDMSAccount column. Let me know if this helps.SELECTFDMSAccountNo = ISNULL(d.[FDMSAccountNo],S.fdmsaccountno),MCC_Code = ISNULL(D.Mcc_Code, s.[FENT-MCC-CODE(4)])FROM [FDMS].[dbo].[Dim_Outlet] dfull join stg_LMPAB501 son d.FDMSAccountNo = s.FDMSAccountNo |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-09-11 : 06:57:34
|
That is exactly what i want :) You are a live saver, this has confused me for hours ha |
|
|
divya.ce
Starting Member
16 Posts |
Posted - 2013-09-11 : 07:48:53
|
I am glad it helped you. Thanks. Divya |
|
|
|
|
|