x

How to use a case statement?

I was trying to insert into a table table1 from table2 using a case statement.Its giving me different results when i change the conditions up and down as shown below.

 insert into table1(id)
 case when t2.code = 'pfi' then 55
       when t2.code = 'sym' then 57
       when t2.code = 'lct' and t2.name like'%abc:' then 51
       when t2.code = 'pdg' and t2.name like '%xyz:' then 56
       when t2.code = 'pdg' then 54
       when t2.code = 'lct' then  53
 from table2 t2

Here only 2 records where inserted into table1 where as if write the conditions up and down its giving me different results

 insert into table1(id)
 case when t2.code = 'pfi' then 55
       when t2.code = 'sym' then 57
          when t2.code = 'pdg' then 54
       when t2.code = 'lct' then  53
      when t2.code = 'lct' and t2.name like'%abc:' then 51
       when t2.code = 'pdg' and t2.name like '%xyz:' then 56
 from table2 t2

its showing like 18 records were inserted.

Can anyone tell me in detail how to use a case statement?Tanks in Advance

more ▼

asked Dec 28, 2010 at 01:02 PM in Default

avatar image

Anand
61 4 5 6

@Anand Both statements do not appear to be valid SQL yet. Could you please clarify what do you want to accomplish?

Dec 28, 2010 at 01:15 PM Oleg

Sorry I missed the 'select' and 'end'words in that.It should be

insert into table1(id) select case when t2.code = 'pfi' then 55 when t2.code = 'sym' then 57 when t2.code = 'lct' and t2.name like'%abc:' then 51 when t2.code = 'pdg' and t2.name like '%xyz:' then 56 when t2.code = 'pdg' then 54 when t2.code = 'lct' then 53 END from table2 t2

insert into table1(id) select case when t2.code = 'pfi' then 55 when t2.code = 'sym' then 57 when t2.code = 'pdg' then 54 when t2.code = 'lct' then 53 when t2.code = 'lct' and t2.name like'%abc:' then 51 when t2.code = 'pdg' and t2.name like '%xyz:' then 56 end from table2 t2

Dec 28, 2010 at 01:19 PM Anand
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It appears that the question does not tell the whole story. Here is why I think so:

Regardless of the order of the items in your case statement, the number of records returned by the select is identical. It is simply equivalent to the number of records in your table named table2. What is selected is a totally different story as the items in your case statement are not exclusive, some conditions do overlap. Try to run both of your queries commenting out the insert into table1 part and adding some columns from table2 to see the difference, but I can tell you without looking at your data that when you compare first and second select, you will notice that while your first select will include results evaluating to 51 and 56, your second select will not include any of these as they will be 53 and 54 instead. The items in the case statement are evaluated from top to bottom, and therefore, in your second select whenever there is a row in table named table2 which has code of lct or pdg, the evaluation will never reach the lines where the additional predicate is specified to also check for name as the previous condition has been already met.

On the top of all this, neither of your case statements include the else part, which is not good, because if none of the conditions are met then the engine has no choice but to include the row anyway, however opt to select null for your result. Please run the following 2 queries to see the difference in your results (I added else in your case statements). I know this does not answer your question why the different number of records is inserted, but it is not because of the case statement, there is something else you did not tell in your question.

 -- query 1
 select 
     t2.code, t2.name, 
     case 
         when t2.code = 'pfi' then 55
         when t2.code = 'sym' then 57
         when t2.code = 'lct' and t2.name like'%abc:' then 51
         when t2.code = 'pdg' and t2.name like '%xyz:' then 56
         when t2.code = 'pdg' then 54
         when t2.code = 'lct' then  53
         else null
     end as result
     from dbo.table2 t2;
 
 -- query 2
 select
     t2.code, t2.name, 
     case 
         when t2.code = 'pfi' then 55
         when t2.code = 'sym' then 57
         when t2.code = 'pdg' then 54
         when t2.code = 'lct' then  53
         when t2.code = 'lct' and t2.name like'%abc:' then 51
         when t2.code = 'pdg' and t2.name like '%xyz:' then 56
         else null
     end as result
     from dbo.table2 t2;
 

You should see that while the number of records returned by both queries above is identical, but the data is not.

Oleg

more ▼

answered Dec 28, 2010 at 04:24 PM

avatar image

Oleg
17.2k 3 7 28

(comments are locked)
10|1200 characters needed characters left

Just from a maintainability / ease-of-understanding point of view, I would be inclined to restructure this so that the instances where you have multiple rules are more clearly shown. Something like:

 case
     when t2.code = 'pfi' then 55
     when t2.code = 'lct' then case when t2.name like '%abc:' then 51 else 53 end
     ...
     else null
 end as result

EDIT: A word of caution - there is a limit of 10 levels of nesting with the CASE statement

more ▼

answered Dec 29, 2010 at 02:00 AM

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

  • for mentioning 10 Levels of nesting

Dec 29, 2010 at 02:20 AM Cyborg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2091
x12

asked: Dec 28, 2010 at 01:02 PM

Seen: 6605 times

Last Updated: Dec 29, 2010 at 03:07 AM

Copyright 2016 Redgate Software. Privacy Policy