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 '10 at 01:02 PM in Default

Anand gravatar image

Anand
61 1 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 '10 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 '10 at 01:19 PM Anand
(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

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 '10 at 04:24 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(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][1] statement

[1]: http://msdn.microsoft.com/en-us/library/ms181765.aspx
more ▼

answered Dec 29 '10 at 02:00 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.4k 14 20 44

+1 for mentioning 10 Levels of nesting
Dec 29 '10 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1816
x39

asked: Dec 28 '10 at 01:02 PM

Seen: 5868 times

Last Updated: Dec 29 '10 at 03:07 AM