Solved

Nested IF statements in SQL

Posted on 2013-12-13
6
432 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
Comment Utility
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
Comment Utility
>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
Comment Utility
Perfect!  Thanks for the Article!!!
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 65

Expert Comment

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

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now