Solved

Nested IF statements in SQL

Posted on 2013-12-13
6
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 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