December 31, 2012 at 9:47 am
Can anyone point out what is wrong with following query
declare @Territory varchar(max)
set @Territory = 'Domestic'
SELECT *
FROM Account
WHERE PrimaryCountry IN (CASE WHEN @Territory = 'Domestic' THEN 'USA, CAN' ELSE PrimaryCountry END)
ORDER BY AccountName
I am getting zero records for Domestic while there is data in the table.
Basically IN ('USA, CAN') is not working. I know the actual syntax should be IN ('USA', 'CAN') if there is no CASE statement, but i have to use the CASE statement.
December 31, 2012 at 10:12 am
If you really need to do this, you might have to resort to dynamic SQL. At the moment, SQL is treating 'USA','CAN' as a single-valued list (so if the country was USA,CAN in the underlying data, you would get a hit).
It would not be difficult to recast your query without the dynamic IN - but I suspect that your actual example may be more complicated, so I won't bother trying that yet.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 31, 2012 at 10:17 am
I don't think there is any possibility using a CASE. I think defining 2 conditions with an OR might work:
SELECT *
FROM Account
WHERE
( @Territory = 'Domestic' and PrimaryCountry in ( 'USA', 'CAN' ) )
OR @Territory <> 'Domestic'
ORDER BY AccountName
December 31, 2012 at 10:25 am
U.T (12/31/2012)
Can anyone point out what is wrong with following query...I am getting zero records for Domestic while there is data in the table.
....
I forgot this question:
you don't get results for Domestic because the query searches for the whole string 'USA, CAN'. That's how the CASE works, it returns the string that you defined.
December 31, 2012 at 11:19 am
U.T (12/31/2012)
Can anyone point out what is wrong with following querydeclare @Territory varchar(max)
set @Territory = 'Domestic'
SELECT *
FROM Account
WHERE PrimaryCountry IN (CASE WHEN @Territory = 'Domestic' THEN 'USA, CAN' ELSE PrimaryCountry END)
ORDER BY AccountName
I am getting zero records for Domestic while there is data in the table.
Basically IN ('USA, CAN') is not working. I know the actual syntax should be IN ('USA', 'CAN') if there is no CASE statement, but i have to use the CASE statement.
I believe you cannot do this because 'USA, CAN' is a single String instead 'USA' ,'CAN' are two different. There is already the corrected query posted above.
Thanks
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
December 31, 2012 at 11:43 am
Thanks everyone, i got it working using OR.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply