August 8, 2008 at 4:06 am
Dear all,
I have a problem with my CASE Statement.
The purpose of the below query is to filter the results buy certain products (A,B,C, ALL)-where all will be A,B,C)
I get the following error when I run the query "Incorrect syntax near ','."
DECLARE @product_name nvarchar(3)
SET @product_name = 'A'
SELECT * FROM dim_product
WHERE source_product_name IN
(
CASE WHEN product_key = 2 and @product_name <> 'A' THEN 'A'
WHEN product_key = 2 and @product_name = 'A' THEN 'B'
WHEN product_key = 4 and @product_name ='C' THEN 'C'
WHEN @product_name = 'All' THEN ('A','B','C')
ELSE ''
END
)
It seems to be failing at the following statement WHEN @product_name = 'All' THEN ('A','B','C')
I cant use a LIKE function like below:
WHERE source_product_name LIKE
(
CASE WHEN product_key = 2 and @product_name <> 'A' THEN 'A'
WHEN product_key = 2 and @product_name = 'A' THEN 'B'
WHEN product_key = 4 and @product_name ='C' THEN 'C'
WHEN @product_name = 'All' THEN '%'
ELSE ''
END
)
because this will get all source_product_names ie E,F, G etc which I dont need.
Is there a way of acheiving this?
Thanks
August 8, 2008 at 5:26 am
Hi
Use this one
DECLARE @product_name nvarchar(3)
SET @product_name = 'A'
SELECT * FROM dim_product
WHERE source_product_name IN
(
CASE WHEN product_key = 2 and @product_name <> 'A' THEN 'A'
WHEN product_key = 2 and @product_name = 'A' THEN 'B'
WHEN product_key = 4 and @product_name ='C' THEN 'C'
WHEN @product_name = 'All' THEN (select 'A' union Select 'B' union Select 'C')
ELSE ''
END
)
Thanks
Vaseem
August 8, 2008 at 6:10 am
Thanks but i get the following error when trying that:
Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
I am assuming in the "THEN" section of the CASE statement you can only return one value :S
August 8, 2008 at 7:28 am
yes, in case of @product_name = 'All' its giving error
but you cannot return list from case statement.
you can use this one for your requirement
DECLARE @product_name nvarchar(3)
SET @product_name = 'All'
SELECT * FROM dim_product
WHERE source_product_name in
(
CASE WHEN product_key = 2 and @product_name <> 'A' THEN 'A'
WHEN product_key = 2 and @product_name = 'A' THEN 'B'
WHEN product_key = 4 and @product_name ='C' THEN 'C'
ELSE ''
END
) and @product_name <> 'All'
or
source_product_name in
(
'A','B','C'
) and @product_name = 'All'
thanks
vaseem
August 8, 2008 at 8:24 am
Try it,
DECLARE @product_name nvarchar(3)
SET @product_name = 'A'
SELECT * FROM dim_product
WHERE (CASE WHEN product_key = 2 and source_product_name='A' And @product_name<>'A' THEN 1 --A
WHEN product_key = 2 and @product_name = 'A' And source_product_name='B' THEN 1 --B
WHEN product_key = 4 and @product_name = source_product_name THEN 1 --C
WHEN @product_name = 'All' and source_product_name in ('A','B','C') THEN 1 ELSE 0 END ) = 1
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com
August 8, 2008 at 8:32 am
Since you want A, B, C, or all three, try the following:
DECLARE @product_name nvarchar(3)
SET @product_name = 'All'
SELECT
*
FROM
dbo.product
WHERE
(product_name = @product_name)
OR ((@product_name = 'All')
AND product_name = 'A'
AND product_name = 'B'
AND product_name = 'C');
😎
August 8, 2008 at 8:42 am
Great work tariq
Its really good logic
August 8, 2008 at 9:19 am
Ive just tired out ur logic vaseem and it works...I will try the next two to see which one gives me greater performance
August 8, 2008 at 9:36 am
Lynn Pettis (8/8/2008)
Since you want A, B, C, or all three, try the following:
DECLARE @product_name nvarchar(3)SET @product_name = 'All'
SELECT
*
FROM
dbo.product
WHERE
(product_name = @product_name)
OR ((@product_name = 'All')
AND product_name = 'A'
AND product_name = 'B'
AND product_name = 'C');
😎
Oops, AND on the brain, this won't work. You need this:
DECLARE @product_name nvarchar(3)
SET @product_name = 'All'
SELECT
*
FROM
dbo.product
WHERE
(product_name = @product_name)
OR ((@product_name = 'All')
AND (product_name = 'A'
OR product_name = 'B'
OR product_name = 'C'));
😎
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply