• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2199
  • Last Modified:

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

how to use SELECT CASE in sql query?please give an example
also how to use IIF in sql query?please give an example
0
umeshmodi
Asked:
umeshmodi
1 Solution
 
rgn2121Commented:
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

0
 
Pratima PharandeCommented:
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

0
 
erikTsomikSystem Architect, CF programmer Commented:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SharathData EngineerCommented:

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

Open in new window

0
 
SwamyNCommented:
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
0
 
Mark WillsTopic AdvisorCommented:
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

0
 
Anthony PerkinsCommented:
And we can all hold our collective breaths as we wait for feedback from the author.
0
 
Mark WillsTopic AdvisorCommented:
Or publish another variant. There is no embedded ones yet...
0
 
Mark WillsTopic AdvisorCommented:
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...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now