Re: WHERE and CASE
From: Adam Machanic (amachanic_at_air-worldwide.nospamallowed.com)
Date: 05/14/04
- Next message: Adam Machanic: "Re: WHERE and CASE"
- Previous message: Nunya: "Re: Help me convince the dev manager. Please."
- In reply to: shank: "WHERE and CASE"
- Next in thread: Joe Celko: "Re: WHERE and CASE"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 14 May 2004 09:47:09 -0400
CASE is an expression, not a statement; so it returns a value.
WHERE CASE @Manuf WHEN LEN(@Manuf)<3 THEN (ItemStock.SoftHard = 'Soft') ELSE
(ItemStock.SoftHard = 'Soft') AND (ItemStock.Manuf = @Manuf) END
This CASE statement does not return a value.
Instead, you could do something like:
WHERE ItemStock.SoftHard = CASE @Manuf WHEN LEN(@Manuf)<3 THEN 'Soft' ELSE
......
However, I think given your logic you'd be better with:
WHERE ItemStock.SoftHard='Soft' AND (LEN(@Manuf)<3 OR
ItemStock.Manuf=@Manuf)
"shank" <shank@tampabay.rr.com> wrote in message
news:uZ9X2dbOEHA.308@TK2MSFTNGP11.phx.gbl...
> I'm having problems using the CASE statement in my WHERE clause. Not even
> sure this is possible. What I'm trying to achieve is if the length of
@Manuf
> is less than 3 characters, then WHERE (ItemStock.SoftHard = 'Soft'),
> otherwise, WHERE (ItemStock.SoftHard = 'Soft') AND (ItemStock.Manuf =
> @Manuf)
> What am I doing wrong here? I get an error near '<' in this clause
> LEN(@Manuf)<3
> thanks!
> -------------------------------------------------------
> DECLARE @Column varchar(50), @Manuf varchar(70)
> BEGIN
> SELECT DISTINCT ItemStock.Manuf, ItemStock.Label, ItemStock.s_Type,
> ItemPrice.ProPrice, ItemPrice.Retail, ItemPrice.SaleBlurb
> INTO #TempNR
> FROM ItemStock INNER JOIN
> ItemPrice ON ItemStock.OrderNo = ItemPrice.OrderNo
> WHERE CASE @Manuf WHEN LEN(@Manuf)<3 THEN (ItemStock.SoftHard = 'Soft')
ELSE
> (ItemStock.SoftHard = 'Soft') AND (ItemStock.Manuf = @Manuf) END
> GROUP BY ItemStock.OrderNo, ItemStock.Label, ItemStock.Manuf,
> ItemStock.s_Type, ItemPrice.ProPrice, ItemPrice.Retail,
> ItemStock.NewRelease, ItemPrice.SaleBlurb
>
> END
>
> BEGIN
> SELECT #TempNR.*
> FROM #TempNR
>
> ORDER BY
> CASE @Column WHEN 'manuf_asc' THEN #TempNR.Manuf + #TempNR.Label +
> #TempNR.s_Type END ASC,
> CASE @Column WHEN 'label_asc' THEN #TempNR.Label END ASC,
> CASE @Column WHEN 'type_asc' THEN #TempNR.s_Type END ASC,
> CASE @Column WHEN 'price_asc' THEN #TempNR.ProPrice END ASC,
> CASE @Column WHEN 'manuf_desc' THEN #TempNR.Manuf END DESC,
> CASE @Column WHEN 'label_desc' THEN #TempNR.Label END DESC,
> CASE @Column WHEN 'type_desc' THEN #TempNR.s_Type END DESC,
> CASE @Column WHEN 'price_desc' THEN #TempNR.ProPrice END DESC
>
> END
> GO
>
>
- Next message: Adam Machanic: "Re: WHERE and CASE"
- Previous message: Nunya: "Re: Help me convince the dev manager. Please."
- In reply to: shank: "WHERE and CASE"
- Next in thread: Joe Celko: "Re: WHERE and CASE"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
