Case Statement Help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case Statement Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-04 : 05:33:28

Hey guys

Hope your well
I need some of you help

Aim > Create a case statement that looks at the Trade_Association_Name and the Account_ID. If the Trade_Association_Name and the Account_ID are the same then “Same” If different then “Check” end as Check

My query is

SELECT Dan.Stg_Jitter_Opp.ID,
Dan.Stg_Jitter_Opp.MID,
Dan.Stg_Jitter_Opp.RecordTypeID,
Dan.Stg_Jitter_Opp.Trade_Association_Name,
Dim_Outlet.Agent_Chain_No,
SalesForce.DailyAccounts.Account_ID
FROM Dan.Stg_Jitter_Opp
INNER JOIN Dim_Outlet ON Dan.Stg_Jitter_Opp.MID = Dim_Outlet.FDMSAccountNo
INNER JOIN SalesForce.DailyAccounts ON Dim_Outlet.Agent_Chain_No = SalesForce.DailyAccounts.MID_Internal
where Agent_Chain_No <> '878970001888'
order by Trade_Association_Name asc

Looking forward to your help/advice

Regards
D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 07:37:29

its straight forward. just convert your explanation into CASE ..WHEN


SELECT Dan.Stg_Jitter_Opp.ID,
Dan.Stg_Jitter_Opp.MID, 
Dan.Stg_Jitter_Opp.RecordTypeID, 
Dan.Stg_Jitter_Opp.Trade_Association_Name, 
Dim_Outlet.Agent_Chain_No, 
SalesForce.DailyAccounts.Account_ID,
CASE WHEN Dan.Stg_Jitter_Opp.Trade_Association_Name = SalesForce.DailyAccounts.Account_ID THEN 'Same' ELSE 'Check' END AS Check
FROM Dan.Stg_Jitter_Opp 
INNER JOIN Dim_Outlet ON Dan.Stg_Jitter_Opp.MID = Dim_Outlet.FDMSAccountNo 
INNER JOIN SalesForce.DailyAccounts ON Dim_Outlet.Agent_Chain_No = SalesForce.DailyAccounts.MID_Internal
where Agent_Chain_No <> '878970001888' 
order by Trade_Association_Name asc

also learn tou use short alisaes
for ex: above query will become


SELECT sjo.ID,
sjo.MID, 
sjo.RecordTypeID, 
sjo.Trade_Association_Name, 
do.Agent_Chain_No, 
da.Account_ID,
CASE WHEN sjo.Trade_Association_Name = da.Account_ID THEN 'Same' ELSE 'Check' END AS Check
FROM Dan.Stg_Jitter_Opp sjo
INNER JOIN Dim_Outlet do ON sjo.MID = do.FDMSAccountNo 
INNER JOIN SalesForce.DailyAccounts da ON do.Agent_Chain_No = da.MID_Internal
where Agent_Chain_No <> '878970001888' 
order by Trade_Association_Name asc

to avoid repeating long names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-04 : 10:04:45

Hi visakh16

Thank you for your post,
When i try and run your code within sql i get the following message

Msg 468, Level 16, State 9, Line 7
Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "Latin1_General_CI_AS" in the equal to operation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 10:13:47

that means one of servers involved has a different collation setting. try this


SELECT sjo.ID,
sjo.MID, 
sjo.RecordTypeID, 
sjo.Trade_Association_Name, 
do.Agent_Chain_No, 
da.Account_ID,
CASE WHEN sjo.Trade_Association_Name collate database_default = da.Account_ID collate database_default THEN 'Same' ELSE 'Check' END AS Check
FROM Dan.Stg_Jitter_Opp sjo
INNER JOIN Dim_Outlet do ON sjo.MID collate database_default = do.FDMSAccountNo collate database_default
INNER JOIN SalesForce.DailyAccounts da ON do.Agent_Chain_No collate database_default = da.MID_Internal collate database_default
where Agent_Chain_No <> '878970001888' 
order by Trade_Association_Name asc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-04 : 10:16:54

Visakh16
You are a * :)

Thank you for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources