SQL CASE Query

Question

0.00/5 (No votes)

See more:

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 a while but being unsuccessful so far.

SQL

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

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 as an account 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.

Posted 3-Jan-11 22:09pm

Yu Ong

Updated 3-Jan-11 23:05pm

Dalek Dave

v3

Add a Solution

Comments

JF2015 4-Jan-11 4:11am

Edited to add code formatting

Dalek Dave 4-Jan-11 5:05am

Edited for Grammar and Readability.

3 solutions

Solution 2

Try

SQL

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

Posted 3-Jan-11 22:26pm

Abhinav S

Updated 3-Jan-11 22:35pm

v3

Comments

Dalek Dave 4-Jan-11 5:06am

Hiren beat you to it by a minute or so, but still a good answer, have a +5.

Abhinav S 4-Jan-11 5:08am

Thanks DD.

Solution 4

SQL

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

The above code should do the deed. Check data types.

Posted 3-Jan-11 22:46pm

Vinayak P Kamath

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hiren solanki · Accepted Answer · 2011-01-03T22:24:00

SQL

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

Try this. I have just removed '=' from ..OMCUST = IN ( SELEC... and some unnecessary semicolons.

Let me know further if it results in any error.