Solved

Nested IF statements in SQL

Posted on 2013-12-13
6
434 Views
Last Modified: 2013-12-13
Good Afternoon experts,

I am writing a query that is pulling customer data and recording it for another system to access.  Most of the data is fine but when it comes to race data I have a problem.

Our system allows for an individual to be a member of more than one race, the target system does not so my database stores the race data this way:

ColumnName           DataType  FieldValue
racewhite                   SmallInt          1 or 0
raceblack                   SmallInt           1 or 0
raceasian                   SmallInt           1 or 0
racenative                 SmallInt           1 or 0
racepacificislander SmallInt           1 or 0

and the target wants:

ColumnName           DataType   FieldValue
Race                            SmallInt      1, 2, 3, 4, 5 (same as the order above 1 if white 2 if black etc)

I know I can do a case statement to say case when racewhite = 1 then 1 end as race but how do I build the same structure as a nested IF for example

IF racewhite = 1 then 1 as race
ELSE IF raceblack = 1 then 2 as race
ELSE IF raceasian = 1 then 3 as race

etc
0
Comment
Question by:ISBTECH
  • 4
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39717653
That would be a nested CASE.

But looking at the three lines at the bottom of your question...

CASE
   WHEN racewhite = 1 THEN 1
   WHEN raceblack = 1 THEN 2
   WHEN raceasian = 1 THEN 3
   END as race

btw This article may help ... SQL Server CASE Solutions
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39717656
>Our system allows for an individual to be a member of more than one race
So .. what does 4 and 5 represent?   I don't see the logic on what value to assign for multiple races.
0
 

Author Closing Comment

by:ISBTECH
ID: 39717691
Perfect!  Thanks for the Article!!!
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 39717710
Thanks for the grade.  Good luck with your project.  -Jim
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39717739
Actually, the challenge you have now is determining which "ONE" of the races each individual would prefer to be included under.

As an example, President Obama would probably check White and Black, but the logic used above would categorize him as white, which is not how I think he would prefer to be categorized.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39717771
Good point fyed ... Given the below block, whichever is the first WHEN expression to hit True, that will be the value passed, So white/asian will always be 1, black/asian will always be 2, etc.

CASE 
   WHEN racewhite = 1 THEN 1
   WHEN raceblack = 1 THEN 2
   WHEN raceasian = 1 THEN 3
   END as race

Open in new window

Perhaps ...
CASE 
   WHEN racewhite + raceblack + raceasian > 1 then 99 -- 'All-american mutt'
   ELSE 
      CASE 
         WHEN racewhite = 1 THEN 1
         WHEN raceblack = 1 THEN 2
         WHEN raceasian = 1 THEN 3
         END as race
   END

Open in new window

0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 57
SQL Replication question 9 41
Sql Server group by 10 26
sqlserver get datetime field and create a string 5 17
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question