How to properly insert records using case statemen

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to properly insert records using case statemen

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-18 : 08:55:03

Hello All,

I'm trying to insert records into a table, based on certain criteria. I decided to use the case statement but I'm having trouble with it. When I excute
the below SQL statement I get this error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.

INSERT INTO A.Table_testing
(COL1, COL2, COL3)
SELECT
CASE WHEN (A.COL2 = 1)
THEN
B.COL1,
B.COL2
((-1) * ISNULL(COL3, 0))
END,
CASE WHEN (A.COL2 = 0)
THEN
B.COL1,
B.COL2,
((100) * ISNULL(COL3, 0))
END
FROM Table_1 AS A FULL OUTER JOIN
Table_1 AS B ON A.COL1 = B.COL1

Does anyone know what I'm doing wrong?

Please advice.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-18 : 08:59:18

[code]
INSERT INTO A.Table_testing
(
COL1,
COL2,
COL3
)
SELECT
B.COL1,
B.COL2,
CASE WHEN (A.COL2 = 1)
THEN -1 * ISNULL(COL3, 0)
ELSE 100 * ISNULL(COL3, 0)
END
FROM
Table_1 AS A
FULL OUTER JOIN
Table_1 AS B ON A.COL1 = B.COL1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-18 : 09:26:18

Thanks for the quick response khtan, I tried your suggestion and it works but if I have more COL(n), how would that work?

Example OF additional fields needed:

CASE WHEN (A.COL2 = 1)
COL4 = 20 ELSE -294
COL5 = 49 ELSE ((120) * ISNULL(COL5, 0))

Please advice.

Thanks.

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-19 : 05:30:31

Thanks for your reply Khtan, but you are missing the "CASE WHEN (A.COL2 = 0)" criteria in your script. Is this where we can use the IF...Then...Else statement?

Please advice.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-19 : 07:49:10

you can't use IF . .THEN in the SELECT QUERY. You have to use CASE WHEN . . .

KH
[spoiler]Time is always against us[/spoiler]

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-19 : 08:02:03

so where would the CASE WHEN (A.COL2 = 0) go in your script?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-19 : 08:38:35

just change it accordingly to your requirement using the correct CASE . . WHEN . . END syntax.

Actually i don't quite understand what exactly you want. If i understand your original query correctly, for whatever condition of A.COL2, you are inserting COL1, COL2 into COL1 and COL2 of Table_testing. The only one that is dependent on the A.COL2 condition is COL3. the value will be (-1) * ISNULL(COL3, 0) OR (100) * ISNULL(COL3, 0). Since it is either one of the possibility if it is A.COL2 = 1 then (-1) * ISNULL(COL3, 0) else it will be (100) * ISNULL(COL3, 0).

Unless you have more complication condition than you will need to code it accordingly.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources