• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

How do I Nest cases or If in CASE MS SQL

I have the following condition in my query
WHERE      StartDate>=@UserDate
      AND
      CASE @Type
            WHEN 1 THEN      CASE WHEN  yearCol  =  @year THEN 1 ELSE 0 END
            WHEN 2 THEN       CASE WHEN  color<>@Color THEN 1 ELSE 0 END
            END = 1
In some case I have @Tpype=2 and @color=null
What I would like to do in the second statement (when 2 then..) check to see if @color is null where I will do something like this
CASE @color
     When null THEN CASE color<>@NewColor THEN 1 ELSE 0 END
     When not Null Then CASE color <> @Color THEN 1 ELSE 0 END
END=1
The above syntax is incorrect what's the right syntax please note that what I am doing in those cases is executing a condition based on the case
Thank you
0
xav056
Asked:
xav056
  • 3
  • 3
  • 3
2 Solutions
 
appariCommented:
try this

WHERE      StartDate>=@UserDate
      AND
      CASE @Type
            WHEN 1 THEN      CASE WHEN  yearCol  =  @year THEN 1 ELSE 0 END
            WHEN 2 THEN      
      CASE
           When @color is null THEN CASE color<>@NewColor THEN 1 ELSE 0 END
                When @color is not Null Then CASE color <> @Color THEN 1 ELSE 0 END
      end
END=1
0
 
cyberkiwiCommented:
Or flat without nesting

WHERE StartDate>=@UserDate
      AND
      CASE  WHEN @Type=1 AND yearCol = @year THEN 1
            WHEN @Type=1 THEN 0
            WHEN @Type=2 AND @color IS NOT NULL AND color<>@Color THEN 1
            WHEN @Type=2 AND @color IS NOT NULL THEN 0
            WHEN @Type=2 AND @color IS NULL AND color<>@NewColor THEN 1
            WHEN @Type=2 AND @color IS NULL THEN 0
      END = 1
0
 
xav056Author Commented:
incorrect syntax wiggly lines under the <> anf then
e.GDID<>@GDID (incorrect syntax bear <> and incorrect syntax near then

Thank you
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
appariCommented:
>>incorrect syntax wiggly lines under the <> anf then
>>e.GDID<>@GDID (incorrect syntax bear <> and incorrect syntax near then
seems the error is now from a different place. post your complete query.
0
 
xav056Author Commented:
appari
nothing before just
Select *
from VehInfo
WHERE      StartDate>=@UserDate
      AND
      CASE @Type
            WHEN 1 THEN      CASE WHEN  yearCol  =  @year THEN 1 ELSE 0 END
            WHEN 2 THEN      
      CASE
           When @color is null THEN CASE color<>@NewColor THEN 1 ELSE 0 END
                When @color is not Null Then CASE color <> @Color THEN 1 ELSE 0 END
      end
END=1
0
 
appariCommented:
missed when,

Select *
from VehInfo
WHERE      StartDate>=@UserDate
      AND
      CASE @Type
            WHEN 1 THEN      CASE WHEN  yearCol  =  @year THEN 1 ELSE 0 END
            WHEN 2 THEN      
      CASE
           When @color is null THEN CASE when color<>@NewColor THEN 1 ELSE 0 END
                When @color is not Null Then CASE when color <> @Color THEN 1 ELSE 0 END
      end
END=1
0
 
xav056Author Commented:
Thank you both
0
 
cyberkiwiCommented:
A properly nested CASE statement - 3 levels, can become really confusing

      CASE
      WHEN @Type=1 THEN
               CASE WHEN yearCol = @year THEN 1 ELSE 0 END
      WHEN @Type=2 THEN
           CASE
           WHEN @color IS NOT NULL THEN
                CASE WHEN color<>@Color THEN 1 ELSE 0 END
           ELSE -- the complement is clearly null
                CASE WHEN color<>@NewColor THEN 1 ELSE 0 END
           END
      END = 1
0
 
cyberkiwiCommented:
Sorry was distracted, here's part 3 of 3.
For your case, you only want @newcolor when @color is null, you can use a better technique instead of CASE

      CASE
      WHEN @Type=1 THEN
           CASE WHEN yearCol = @year THEN 1 ELSE 0 END
      WHEN @Type=2 THEN
           CASE WHEN color<>Coalesce(@Color,@NewColor) THEN 1 ELSE 0 END
      END = 1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now