CASE statement help

I'm receiving a parameter@.
@.deletions AS CHAR(1)
I want to use this in a select case statement:
--
SELECT
columnA,
columnB,
columnC
FROM
tableA
WHERE
columnC = 'a',
AND
columnD = 'b'
CASE @.deletions
WHEN 'N' THEN AND (Catalogue_Product.Update_Code <> 'D')
END
--
It doesn't seem to like this case statement in the WHERE section...it's
happy with it in the SELECT section.
Any ideas?
Thanks
GriffLet's start off with the basics of programming. There is no CASE
statement in SQL; there is a CASE **expression**; expression return
values. You are still trying to write a procedural language and SQL is
a declarative language.
Guessing at what you meant, try something like this:
SELECT columnA, columnB, columnC
FROM TableA , Catalogue_Products AS P
WHERE columnC = 'a',
AND columnD = 'b'
AND CASE
WHEN @.deletions = 'N'
AND P.update_code <> 'D'
THEN 'N' ELSE 'Y' END = 'Y' ;|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115142172.036337.315110@.z14g2000cwz.googlegroups.com...
>SQL is a declarative language.
Apologies for use of wrong terminology...
Okay, not sure I explained what I was attempting to do particularly well, so
attempt #2
I want my WHERE statement to behave in one of two ways:
EITHER
WHERE colC='a' AND colD='b'
OR
WHERE colC='a' AND colD='b' AND colE<>'d'
So, the WHERE statement will either have the "AND colE<>'d'" bit or it
won't.
I thought of using a SELECT EXPRESION based on an input parameter to control
this, but it doesn't appear to like it.
For example
WHERE
colC='a'
AND
colD='b'
CASE @.myInputParameter
WHEN 'n' THEN AND colE<>'d'
END
Griff|||DECLARE @.myInputParameter CHAR(1)
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
AND ((@.myInputParameter = 'N' AND columnE <> 'D')
OR (@.myInputParameter <> 'N'))
You'll probably get better performance out of it (index issues may surface
from using OR, <> and variables in your WHERE clause) if you split it up
into two possible queries based on an IF statement:
DECLARE @.myInputParameter CHAR(1)
IF @.myInputParameter = 'Y'
BEGIN
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
END
ELSE
BEGIN
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
AND columnE <> 'D'
END
"Griff" <Howling@.The.Moon> wrote in message
news:OMdEHmAUFHA.3840@.tk2msftngp13.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1115142172.036337.315110@.z14g2000cwz.googlegroups.com...
> Apologies for use of wrong terminology...
> Okay, not sure I explained what I was attempting to do particularly well,
> so attempt #2
> I want my WHERE statement to behave in one of two ways:
> EITHER
> WHERE colC='a' AND colD='b'
> OR
> WHERE colC='a' AND colD='b' AND colE<>'d'
> So, the WHERE statement will either have the "AND colE<>'d'" bit or it
> won't.
> I thought of using a SELECT EXPRESION based on an input parameter to
> control this, but it doesn't appear to like it.
> For example
> WHERE
> colC='a'
> AND
> colD='b'
> CASE @.myInputParameter
> WHEN 'n' THEN AND colE<>'d'
> END
> Griff
>
>
>|||Direct translation into SQL:
SELECT columnA, columnB, columnC
FROM TableA
WHERE CASE WHEN colC =' a' AND colD =' b' AND @.my_switch = 'Y'
THEN 'Y'
WHEN colC =' a' AND colD =' b' AND colE<>'d'
AND @.my_switch = 'N'
THEN 'Y' ELSE 'N' END = ' Y";
You can factor out the common sub-expression predicates, but this might
be easier to read and maintain.sql