Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Dynamic IN clause
Add to briefcase

Dynamic IN clause Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
U.T
Posted Monday, December 31, 2012 9:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:35 PM
Points: 224, Visits: 407
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.
Post #1401424
Phil Parkin
Posted Monday, December 31, 2012 10:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:39 AM
Points: 4,247, Visits: 9,500
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.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1401430
WolfgangE
Posted Monday, December 31, 2012 10:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 1:01 AM
Points: 74, Visits: 420
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
Post #1401433
WolfgangE
Posted Monday, December 31, 2012 10:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 1:01 AM
Points: 74, Visits: 420
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.
Post #1401436
CELKO
Posted Monday, December 31, 2012 11:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it.

I see by the singular name, that you have only one account! Or you have never read a book on data modeling

Why did you use VARCHAR(MAX)? It just invites garbage data, cannot be optimized, does not port, etc? What research did you do on this? What is the longest country name in the ISO Standards? Look it up.

The ISO country_code that is CHAR(3), and it dose not include a place named 'USA-CAN' in that list. In fact, they are two different countries with different laws, currency,flags, etc. If you want to lump them together, then do it in the front end and not the database – databases need truth.

Your approach to SQL is to write COBOL or BASIC. This is a declarative language. The term “primary_country” is not a special kind of country; it is a designation. Here is a skeleton:

CREATE TABLE Accounts
(account_nbr CHAR(15) NOT NULL PRIMARY KEY,
..);

CREATE TABLE Account_Territories
(account_nbr CHAR(15) NOT NULL
REFERENCES Accounts(account_nbr),
country_code CHAR(3) NOT NULL,
PRIMARY KEY (account_nbr, country_code),
UNIQUE (account_nbr, territory_priority),
territory_priority INTEGER DEFAULT 0 NOT NULL
CHECK (territory_priority > 0),
..);

We can now display whatever you want. I would create VIEWs for common reports, such as:

SELECT A.*
FROM Accounts AS A, Account_Territories AS T
WHERE A.account_nbr = T.account_nbr
AND T.territory_priority = 1
AND T.country_code = ('USA', 'CAN');

I am getting zero records [sic] for Domestic while there is data in the table .. but I have to use the CASE statement [sic].


Wrong again. Rows are not records. CASE is an expression and not a statement. What compels you to use CASE? Will they shoot you? Put you in Jail?


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1401452
a4apple
Posted Monday, December 31, 2012 11:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:05 PM
Points: 39, Visits: 87
U.T (12/31/2012)
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.



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
Post #1401457
U.T
Posted Monday, December 31, 2012 11:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:35 PM
Points: 224, Visits: 407
Thanks everyone, i got it working using OR.
Post #1401461
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.