Link to home
Create AccountLog in
Avatar of dshi15
dshi15Flag for United States of America

asked on

Case statement inside select

I have
SELECT * From Programs WHERE
(CASE WHEN @Program ! = 'All' THEN Programs.Programcode = @Programcode ELSE 1=1 END) AND
(CASE WHEN @ProgramType ! = 'All' THEN Programs.ProgramType = @ProgramTye ELSE 1=1 END)

What is wrong with this? I got an error said  Incorrect syntax near '='.

thanks.


Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi, try this

SELECT * From Programs WHERE
(CASE WHEN @Program <> 'All' THEN Programs.Programcode = @Programcode ELSE 1=1 END) AND
(CASE WHEN @ProgramType <> 'All' THEN Programs.ProgramType = @ProgramTye ELSE 1=1 END)
you can also do it like this

SELECT * From Programs WHERE
(@Program = 'All' OR Programs.Programcode = @Programcode) AND
(@ProgramType = 'All' OR Programs.ProgramType = @ProgramType)
Avatar of dshi15

ASKER

I tried this not work either,

SELECT * From Programs WHERE
(CASE WHEN @Program <> 'All' THEN Programs.Programcode = @Programcode ELSE 1=1 END) AND
(CASE WHEN @ProgramType <> 'All' THEN Programs.ProgramType = @ProgramTye ELSE 1=1 END)

I used  !='All'  in other place, it works.
what is the error when you use <>?

btw, there is a mistype on the query -> @ProgramTye
Avatar of dshi15

ASKER

and I need add one more, so that is why I use case statament.

SELECT * From Programs WHERE
(CASE
WHEN @Program ! = 'All' THEN Programs.Programcode = @Programcode
WHEN @Program  = 'NoData' THEN Programs.Programcode IsNull Or Programs.Programcode =''
ELSE 1=1
END)
AND
(CASE WHEN @ProgramType <> 'All' THEN Programs.ProgramType = @ProgramTye ELSE 1=1 END)

Avatar of dshi15

ASKER

I will correct typo and try again.
-------------------------v
>WHEN @Program ! = 'All' THEN Programs.Programcode = @Programcode

you have a space between !=, try to remove it.
Try this...

SELECT * From Programs WHERE
Programs.Programcode = (CASE WHEN @Program <> 'All' THEN @ProgramCode ELSE Programs.ProgramCode END)
AND Programs.ProgramType = (CASE WHEN @ProgramType <> 'All' THEN @ProgramTye ELSE Programs.ProgramType END)
Avatar of dshi15

ASKER

-------------------------v
>WHEN @Program ! = 'All' THEN Programs.Programcode = @Programcode

you have a space between !=, try to remove it.

I tried and I corrected typo, it still a error and same error.
This is to resolve the comment you made when you wanted to add one more criteria to check for "nodata"

SELECT * From Programs WHERE
(
Programs.Programcode =
      (CASE
            WHEN @Program  = 'NoData' THEN ''
            WHEN @Program <> 'All' THEN @ProgramCode
            ELSE Programs.ProgramCode
      END)
OR (@Program  = 'NoData' AND Programs.ProgramCode IS NULL)
)
AND Programs.ProgramType = (CASE WHEN @ProgramType <> 'All' THEN @ProgramTye ELSE Programs.ProgramType END)

Avatar of dshi15

ASKER

sorry, I didn't go that far yet.

SELECT * From Programs WHERE
Programs.Programcode = (CASE WHEN @Program <> 'All' THEN @ProgramCode ELSE Programs.ProgramCode END)
AND Programs.ProgramType = (CASE WHEN @ProgramType <> 'All' THEN @ProgramType ELSE Programs.ProgramType END)

This part is not work, I got the same error said  Incorrect syntax near '='.

When I execute the following, I don't get any errors.  What line causes the error for you?

DECLARE @Program VARCHAR(20), @ProgramType VARCHAR(20), @ProgramCode VARCHAR(20)

SET @Program = 'All'
SET @ProgramType = 'All'
SET @ProgramCode = 'test'

SELECT * From Programs WHERE
Programs.Programcode = (CASE WHEN @Program <> 'All' THEN @ProgramCode ELSE Programs.ProgramCode END)
AND Programs.ProgramType = (CASE WHEN @ProgramType <> 'All' THEN @ProgramType ELSE Programs.ProgramType END)
Should there be 3 variables or should this be changed to...

@ProgramCode <> 'All' THEN @ProgramCode
Avatar of dshi15

ASKER

Thanks both of you.

SELECT * From Programs WHERE
Programs.Programcode = (CASE WHEN @Program <> 'All' THEN @ProgramCode ELSE Programs.ProgramCode END)
AND Programs.ProgramType = (CASE WHEN @ProgramType <> 'All' THEN @ProgramType ELSE Programs.ProgramType END)

it works.

I need make my question clear. Now if @Program='NoData", I just want select Programcode is Null.

(ProgramCode from a dropdown list)


Avatar of dshi15

ASKER

sorry for previous typo, because I have very long statement, a lot of AND Case statements.
In my previous comment above I gave you the solution to check for if it is null.  Here it is again.

SELECT * From Programs WHERE
(
Programs.Programcode =
      (CASE
            WHEN @Program  = 'NoData' THEN ''
            WHEN @Program <> 'All' THEN @ProgramCode
            ELSE Programs.ProgramCode
      END)
OR (@Program  = 'NoData' AND Programs.ProgramCode IS NULL)
)
AND Programs.ProgramType = (CASE WHEN @ProgramType <> 'All' THEN @ProgramTye ELSE Programs.ProgramType END)

SELECT * From Programs WHERE
(Programs.Programcode = (CASE WHEN @Program = 'NoData' THEN NULL WHEN @Program <> 'All' THEN @ProgramCode ELSE Programs.ProgramCode END)
AND Programs.ProgramType = (CASE WHEN @ProgramType <> 'All' THEN @ProgramType ELSE Programs.ProgramType END))
OR (@Program = 'NoData' AND Programs.Programcode IS NULL)

Open in new window

I believe that this is also a separate question than what you originally posted.  Your original question was "What is wrong with this? I got an error said  Incorrect syntax near '='." and I believe I have answered that one.
ee_rlee, this is incorrect.  You can not check for a null statement with the "=" sign.  It has to be ProgramCode is not null

Incorrect:  ProgramCode = NULL
Correct: ProgramCode IS NULL
Avatar of dshi15

ASKER

Thank you very much both of you, I need test my code then split the point.

I don't understand this part.

SELECT * From Programs WHERE
(
Programs.Programcode =
      (CASE
            WHEN @Program  = 'NoData' THEN ''
            WHEN @Program <> 'All' THEN @ProgramCode
            ELSE Programs.ProgramCode
      END)
OR (@Program  = 'NoData' AND Programs.ProgramCode IS NULL)
)

Can I change to

SELECT * From Programs WHERE
(
Programs.Programcode =
      (CASE
            WHEN @Program  = 'NoData' THEN ''
            WHEN @Program <> 'All' THEN @ProgramCode
            ELSE Programs.ProgramCode
      END)
OR (@Program  = 'NoData' THEN Programs.ProgramCode IS NULL)
)

I changed "And" to "Then", because when use select NoData in dropdown, I want show only programs with Programs.ProgramCode IS NULL.

SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dshi15

ASKER

it is already beyond original question,  I will test code, I think I need submit another question for this If it not work. Thanks,