question

artistlover avatar image
artistlover asked

insert with case statements

I am inserting data from one table into another then adding additional calculated fields. insert into xxx field1, field2, field3, field4, field5 values field1 ,case when x < y then 'va' else case when x>y then 'mn' else 'vv'end' end , field3, case, field5 from tableq Does it work like that. Meaning can i have case from inside the insert like in example above because i have five more fields to insert that will all need case statement. I created the first case statement and it works as i need it to but that was just selecting the return to make sure it worked.
case
5 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.

@artistlover -- how about INSERT INTO table_dest SELECT all CASE statements here FROM table_source. What prevents you from doing this?
3 Likes 3 ·
@artistlover As @DenisT said, if your select statement already works then all you need to do is replace the word ***values*** with the word ***select***, that is all. Also, there is no need to nest the case statements like this. You should not do this: case when x < y then 'va' else case when x > y then 'mn' else 'vv' end end What you should do instead is this: case when x < y then 'va' when x > y then 'mn' else 'vv' end Finally, the list of column after xxx needs parentheses around it. Here is the sample script based on your sample in question: insert into xxx (field1, field2, field3, field4, field5) select field1, case when x < y then 'va' when x > y then 'mn' else 'vv'end as field2, field3, case a when 5 then 'Five' when 7 then 'Seven' else 'whatever' end as field4, field5 from tableq Hope this helps.
0 Likes 0 ·
Thank you and I am just dealing with divide by zero error. Which i used nullif.
0 Likes 0 ·
@artistlover Yes, this is one of the ways to handle it, i.e. instead of running select ColA / ColB from SomeTable; and risking divide by zero error should any row have 0 in ColB column, it is possible to run this instead: select ColA / nullif(ColB, 0) from SomeTable; Some purists advise against **nullif** because it is syntactic sugar and suggest spelling it out instead, i.e. select ColA / case when ColB = 0 then null else ColB end from SomeTable; but I usually don't follow this advice and use **nullif** anyway. Also, just for sanity check, to avoid the integer division problem should both columns be integers, I usually multiply the top part by 1.0 to ensure that 7 / 4 gives me the 1.75 I crave rather than 1 which happens when both numbers are integers, so the query may actually look like this: select 1. * ColA / nullif(ColB, 0) from SomeTable; just in case :)
0 Likes 0 ·
@oleg -- This is exactly what's done behind the scenes NULLIF == CASE WHEN ColB = 0 THEN NULL ELSE ColB END. NULLIF is just faster to write :)
0 Likes 0 ·

0 Answers

·

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.