question

Anand avatar image
Anand asked

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
sql-server-2008case-statement
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@Anand Both statements do not appear to be valid SQL yet. Could you please clarify what do you want to accomplish?
0 Likes 0 ·
Anand avatar image Anand commented ·
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
0 Likes 0 ·
Oleg avatar image
Oleg answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image Cyborg commented ·
+1 for mentioning 10 Levels of nesting
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.