Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Nested IF statements in SQL

Posted on 2013-12-13
6
Medium Priority
?
441 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 2000 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

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.
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…
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 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