SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 09/11/2013 :  06:05:25  Show Profile  Reply with Quote
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_Code
into #MCCDim_Outlet
FROM [FDMS].[dbo].[Dim_Outlet]

--MCC Build 501--
SELECT
stg_LMPAB501.FDMSAccountNo,
[FENT-MCC-CODE(4)]
into #MCC501
FROM stg_LMPAB501


--Joining DimOutlet MCC & 501 Mcc tables together --
SELECT *
into #MCC
FROM #MCCDim_Outlet
UNION
SELECT *
FROM #MCC501

But i have now hit a brick wall, would appreciate any help avaialble



Edited by - masond on 09/11/2013 06:25:34

divya.ce
Starting Member

India
16 Posts

Posted - 09/11/2013 :  06:54:58  Show Profile  Reply with Quote
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.
SELECT
FDMSAccountNo = ISNULL(d.[FDMSAccountNo],S.fdmsaccountno),
MCC_Code = ISNULL(D.Mcc_Code, s.[FENT-MCC-CODE(4)])
FROM [FDMS].[dbo].[Dim_Outlet] d
full join stg_LMPAB501 s
on d.FDMSAccountNo = s.FDMSAccountNo
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 09/11/2013 :  06:57:34  Show Profile  Reply with Quote
That is exactly what i want :)

You are a live saver, this has confused me for hours ha
Go to Top of Page

divya.ce
Starting Member

India
16 Posts

Posted - 09/11/2013 :  07:48:53  Show Profile  Reply with Quote
I am glad it helped you. Thanks. Divya
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000