CODEWITHSUNDEEP
sqlsql-serversyntax
Nard Dog
Nard Dog

Reputation: 916

Quick SQL Syntax Question

I have a standard insert into / select statement that is formatted similar to this:

insert into Table
( ...,
 ...)

select
field1,
field2,
field3,
field4,
fieldetc
from .... etc

There are three specific fields in the select statement that will need different values selected depending on another field, let's call it checker and the three fields are field2, field3, and field 4. The values will either be a 0 or in the other situation will need a case when. My question is, how do I format an if/else statement so it will work within the select statement? How I have it now is like this:

select
field1data,
if checker = 'A' or checker is null
begin
  0,
  0,
  0,
end
else 
begin
case when ... then ... else ... end,
case when ... then ... else ... end,
case when ... then ... else ... end,
end
fieldetcdata
from... etc

This is returning errors. How can I format this so it will work correctly, either selecting zeroes for these three fields or running through my case when statements in the other situation. Thanks!

Upvotes: 3

Views: 91

Answers (3)

YetAnotherUser
YetAnotherUser

Reputation: 9346

You'll need to specify case statement for each field separately.

Select  field1data,

        Case When IsNull(Checker,'A') = 'A' Then 0
             When Cond1 Then Whatever1
             ...
             Else ...
        End,

        Case When IsNull(Checker,'A') = 'A' Then 0
             When Cond2 Then Whatever1
             ...
             Else ...
        End,

        Case When IsNull(Checker,'A') = 'A' Then 0
             When Cond2 Then Whatever1
             ...
             ELSE ...
        End,

        fieldetcdata
From    ETC

Upvotes: 5

Matthew
Matthew

Reputation: 10444

Take out the IF and the BEGIN/END stuff and it should work. All you need to use is the

 CASE COALESCE(checker,'A') WHEN 'A' THEN 0 ELSE alternate_value END

for each conditional value you want to SELECT

EDIT: Using your example:

SELECT
    field1data,
    CASE WHEN ISNULL(checker) THEN alternate_value1 
         WHEN checker = 'B' THEN alternate_value11 END,
    CASE WHEN ISNULL(checker) THEN alternate_value2 
         WHEN checker = 'B' THEN alternate_value22 END,
    CASE WHEN ISNULL(checker) THEN alternate_value3 
         WHEN checker = 'B' THEN alternate_value3 END,
    fieldetcdata
FROM
    TABLE

EDIT2: For multiple conditions, you simply add WHEN clauses.

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Upvotes: 3

Steve Prentice
Steve Prentice

Reputation: 23514

Edited based on comments below.

You need to use a CASE statement with multiple WHEN conditions.

SELECT
  field1data,
  CASE COALESCE(checker, 'A') WHEN 'A' THEN 0 WHEN condition2 THEN ... ELSE ... END,
  CASE COALESCE(checker, 'A') WHEN 'A' THEN 0 WHEN condition2 THEN ... ELSE ... END,
  CASE COALESCE(checker, 'A') WHEN 'A' THEN 0 WHEN condition2 THEN ... ELSE ... END,
  CASE COALESCE(checker, 'A') WHEN 'A' THEN 0 WHEN condition2 THEN ... ELSE ... END,
  CASE COALESCE(checker, 'A') WHEN 'A' THEN 0 WHEN condition2 THEN ... ELSE ... END
FROM ...

Upvotes: 1

Related Questions