x

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

more ▼

asked Jan 23, 2013 at 01:01 AM in Default

avatar image

redhat69
10 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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')

more ▼

answered Jan 23, 2013 at 07:41 AM

avatar image

ThomasRushton ♦♦
42k 20 50 53

(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.

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:

x159
x4
x0

asked: Jan 23, 2013 at 01:01 AM

Seen: 1605 times

Last Updated: Jan 23, 2013 at 07:41 AM

Copyright 2017 Redgate Software. Privacy Policy