We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

how to use SELECT CASE in sql query?please give an example

Medium Priority
2,220 Views
Last Modified: 2012-05-06
how to use SELECT CASE in sql query?please give an example
also how to use IIF in sql query?please give an example
Comment
Watch Question

Commented:
Here is something where I use the Select Case...
SELECT X.item1, X.item2, X.item3,
X.item4,
(CASE
             WHEN TRIM(color1) <> ' '
                THEN color1
             ELSE CASE
             WHEN TRIM(color2) <> ' '
                THEN color2
             ELSE CASE
             WHEN TRIM(color3) <> ' '
                THEN color3
             ELSE CASE
             WHEN TRIM(color4) <> ' '
                THEN color4
             ELSE CASE
             WHEN TRIM(color5) <> ' '
                THEN color5
             ELSE color6
          END
          END
          END
          END
          END
         ) color
...finishing items....

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Commented:
Try this

F1 ,f2 F3 are field names
select 
					 CASE  WHEN (F1 > 0)
			 THEN 
				F1
			 WHEN  (F2 > 0)
			 THEN 
				F2
			 ELSE
			    F3
			  END
			From  TABLENAME

Open in new window

erikTsomikSystem Architect, CF programmer

Commented:
SharathData Engineer
CERTIFIED EXPERT

Commented:

IIF(condition, <value if condition is true>,<value if condition is false>)
 
SELECT IIF(10 > 5, 1,0)

Open in new window

Commented:
hi umesh, try this
select case Column_Name when '1' then 'Yes' when '0' then 'No' else 'NA' end from Table_Name
The above query returns Yes, No or NA if the actual values of 'COlumn_Name' are 1, 0 or anything else respectively
For the MSSQL Server equivalent of IIF, you have to use case as
select case when CONDITION then  value1 else value2 end  from TABLES USED IN CONDITION
eg:
select case when 1 + 1= 2 then 'True' else 'False' end
select case when Column_Name = 1 then 'True' else 'False' end  from Table_Name
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
a couple of ways to use a case statement (and you can imbed them as well)

case condition when result then value
                        when result then value
                         else othervalue
                         end

or

case when condition then do_this_if_true else do_that end

or

case when condition then value
        when condition then value
        else other value
        end


For example,


select case when datename(dw,getdate()) like 'S%' then 'Weekend' else 'Workday' end as type_of_day
 
select case left(datename(dw,getdate()),2) when 'SU' then 'Church'
                                           when 'SA' then 'Golf'
                                           else 'Work'
                                           end as main_activity
 
 
-- or from a table...
 
select table_name, column_name, data_type+case when data_type like '%char%' then '('+convert(varchar,character_maximum_length)+')'
                                               when data_type = 'decimal' then '('+convert(varchar,numeric_precision)+','+convert(varchar,isnull(numeric_scale,0))+')'
                                               else '' end as type_of_column
from information_schema.columns order by table_name, ordinal_position 

Open in new window

CERTIFIED EXPERT
Top Expert 2012

Commented:
And we can all hold our collective breaths as we wait for feedback from the author.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Or publish another variant. There is no embedded ones yet...
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Looks like I was wrong in the previous post, because the only one worthy of 50 points with all the great examples, is the embedded one...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.