Solved

Nested IF statements in SQL

Posted on 2013-12-13
6
440 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 66

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 66

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
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
LVL 66

Expert Comment

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

Expert Comment

by:Dale Fye
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 66

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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
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.
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…

627 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