SQL server Case query - 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

SQL server Case query

Author

Topic

Yu Ong
Starting Member

3 Posts

Posted - 2011-01-04 : 03:33:35

Hi

I need help in writing a case script in SQL but I am a beginner so I am doing a lot of cutting an pasteing from existing scripts and changing the variables

I have been trying to get this to work for awhile but being unsuccessful so far.

SELECT OMCOMP,OMCUST, AccountFrom =
CASE
when OMCOMP = 'SG1' and OMDIM1 = 1000 or (SELECT Account FROM Finance_APAC.dbo.testAccount WHERE Country = 'SG1' AND OMCUST.fmo = Account ) THEN '1'
when OMCOMP = 'SG1' and OMDIM1 = 1234 or (SELECT Account FROM Finance_APAC.dbo.testAccount WHERE Country = 'SG1' AND OMCUST.fmo = Account ) THEN '2'
ELSE 'OTHERS'
END
FROM Finance_APAC.dbo.testFMOSAL.fmo

Basically I want to create a table that would show 'AccountFrom' either in '1' or '2' or ' Others'

In the first instance, if OMCOMP is 'SG1' and OMDIM1 is 1000 or if OMCOMP is 'SG1' and OMCUST is exists in another table where country is SG1 then Accountfrom will be equal to 1

Thanks very much in advance for any expertise anyone can offer on my issue.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 03:37:45

SELECT OMCOMP,OMCUST, AccountFrom =
CASE
when OMCOMP = 'SG1' and OMDIM1 = 1000 or exists (SELECT * FROM Finance_APAC.dbo.testAccount WHERE Country = 'SG1' AND OMCUST.fmo = Account ) THEN '1'
when OMCOMP = 'SG1' and OMDIM1 = 1234 or exists (SELECT * FROM Finance_APAC.dbo.testAccount WHERE Country = 'SG1' AND OMCUST.fmo = Account ) THEN '2'
ELSE 'OTHERS'
END
FROM Finance_APAC.dbo.testFMOSAL.fmo

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-04 : 03:37:58

Please explain ... are you facing any error while executing the code - if yes, paste the error along with query you have executed.

If not then explain with the help of example e.g. the data layout in the tables and the required output...

Cheers!
MIK

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 03:42:46

Not quite sure what you are loking for as I think your code probably hs some mistakes but probably better is something like

SELECT OMCOMP,OMCUST, AccountFrom =
CASE
when OMCOMP = 'SG1' and (OMDIM1 = 1000 or Account.Account is not null)) THEN '1'
when OMCOMP = 'SG1' and (OMDIM1 = 1234 or Account.Account is not null)) THEN '2'
ELSE 'OTHERS'
END
FROM Finance_APAC.dbo.testFMOSAL fmosal
left join (SELECT distinct Account FROM Finance_APAC.dbo.testAccount WHERE Country = 'SG1') Account
on fmosal.fmo = Account.Account

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources