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.
Here only 2 records where inserted into table1 where as if write the conditions up and down its giving me different results
its showing like 18 records were inserted.
Can anyone tell me in detail how to use a case statement?Tanks in Advance
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.
You should see that while the number of records returned by both queries above is identical, but the data is not.
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:
EDIT: A word of caution - there is a limit of 10 levels of nesting with the CASE statement