Quick SQL Syntax Question

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!

"returning errors" - What errors? It would help if you specify them. — Hans Keﬆing
– Hans Keﬆing, Commented Jun 9, 2011 at 20:36
After thinking about it more, I mean I don't mean to be a dick but what errors could you possibly think I could mean? I mean, I know I'm a newbie but it's titled syntax question, and if you have been using SQL any longer than I have then you would probably immediately see this would throw an error...so....really? Did you even look at the code or did you just read error and decided to post that? I don't really do this, but that kind of struck a nerve with me so I'm flagging it as not constructive. — Nard Dog
– Nard Dog, Commented Jun 9, 2011 at 21:27

YetAnotherUser · Accepted Answer · 2011-06-09 21:25:29Z

5

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

edited Jun 9, 2011 at 21:25

answered Jun 9, 2011 at 20:35

YetAnotherUser

9,3863 gold badges42 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nard Dog Over a year ago

You are getting a lot of upvotes but that throws a syntax error when I try it. If you take out the else on the second line it will work.

Matthew Over a year ago

@Nard Dog, you are correct. The ELSE before the second WHEN is incorrect syntax (in SQL Server, at least)

YetAnotherUser Over a year ago

@Nard, @Matthew: Thanks, fixed it. It was a back-of-envelop-code-syndrome.

Matthew · Accepted Answer · 2011-06-09 21:09:39Z

3

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

edited Jun 9, 2011 at 21:09

answered Jun 9, 2011 at 20:36

Matthew

10.5k5 gold badges53 silver badges107 bronze badges

5 Comments

Nard Dog Over a year ago

Unfortunately the alternative_value needs to be either one of two values, depending on the checker's value if it doesn't equal 'A', I don't see any other way around not having another embedded case/when unless you can show me how to do an additional check on checker and then depending on that use either value 'B' or 'C' for alternative_value1 instead.

Matthew Over a year ago

I edited my answer. You should not use embedded CASE statements. Just add more WHEN clauses.

Nard Dog Over a year ago

I see now how it works, I didn't realize you could do multiple when checks within the same case, that would have made things much easier had I known that in the beginning! Also, the first check in reality is checking if it is null so I don't know if coalesce is necessary, but it's my fault for not explaining that and throwing a value in it's place instead. I was simply trying to get the basic syntax down right.

Matthew Over a year ago

@Nard Dog Just make sure when formatting this way that you have boolean conditions. I have changed COALESCE to ISNULL which I think is more readable... personal preference.

Nard Dog Over a year ago

Thanks for your help, you have been invaluable on my quest for knowledge!

Steve Prentice · Accepted Answer · 2011-06-09 20:57:50Z

1

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

edited Jun 9, 2011 at 20:57

answered Jun 9, 2011 at 20:42

Steve Prentice

23.6k11 gold badges56 silver badges56 bronze badges

4 Comments

Matthew Over a year ago

-1 He should not embed the CASE statements, he should just make the equality Checker = x the WHEN condition, which can be repeated: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2.....

Nard Dog Over a year ago

This is perfect, I don't think the other guy sees what I'm trying to say as I have two different values that need to be checked with the checker, if the value for the first check is not correct then it needs to go through the second case/when to check for a different value. It is a requirement to have the first check for 'A', then if that passes set it to 0, else check checker if equal to value 'B', then thirdvalue else finalvalue. I'm not great at explaining so it's my fault but the second case/when is definitely necessary and I see no way around for that many checks that are needed.

Steve Prentice Over a year ago

Mathew PK has a good point, though. I'll update my answer based on his feedback, but also keeping your case in mind. His answer also included the necessary COALESCE so I'll throw that in too.

Nard Dog Over a year ago

It was my fault for putting a value there, but the first check for 'A' was actually checking for null instead of a value, I just typed 'A' to try and explain what I was doing to get the basic syntax right, I'm not sure if COALESCE is necessary.

Collectives™ on Stack Overflow

Quick SQL Syntax Question

3 Answers 3

3 Comments

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related