question

redhat69 avatar image
redhat69 asked

CASE WHEN FIELD IN(..) SELECT Statement

Trying to create a condition wherein if a particular field matches then use a formula. Example: Insert into #table1 SELECT CASE WHEN fld1 IN ('A','B','C') THEN fld2 = '1', fld3 = '2' WHEN fld1 IN ('D','E','F') THEN fld2 = '2', fld3 = '1' ELSE END FROM SomeTable
selectincase
10 |1200

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

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
So you're trying to set both Fld1 and fld2 based on the value in fld1? Two ways of doing this come to my mind. First, use a single CASE statement for each of fld2 & fld3: INSERT INTO #Table1 SELECT CASE WHEN fld1 IN ('A', 'B', 'C') THEN '1' WHEN fld1 IN ('D', 'E', 'F') THEN '2' END AS fld2, CASE WHEN fld1 IN ('A', 'B', 'C') THEN '2' WHEN fld1 IN ('D', 'E', 'F') THEN '1' END AS fld3 FROM SomeTable Alternatively, you could try: INSERT INTO #Table1 SELECT '1' AS fld2, '2' AS fld3 FROM SomeTable WHERE fld1 IN ('A', 'B', 'C') UNION SELECT '2' AS fld2, '1' AS fld3 FROM SomeTable WHERE fld1 IN ('D', 'E', 'F')
10 |1200

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

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.