Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using the IN operator with two variable Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 7, 2013 8:05 AM
Points: 2, Visits: 3
I would like to explain my problem with a simple example.

Here is a part of my database:

Type Service
----- -----
Freshwater Habitat
Saltwater Food
Dunes Recreation

That's the query I have: (It's HQL which is similar to T-SQL)

from Gecoserv _a where _a.typeOfEcosystem IN (:typeNameList) AND _a.service IN (:serviceNameList)

I want the grid to show only the single combinations (Freshwater-Habitat, Saltwater-Food and Dunes-Recreation etc.) but it shows also other combinations (ex: Freshwater-Recreation).

How can I fix my problem ?

Post #1398031
Posted Tuesday, December 18, 2012 1:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 13,093, Visits: 12,573
Hi and welcome to SSC. It is pretty difficult to provide a detailed answer for your question without speculating quite a few things. You might want to take a quick look at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1398037
Posted Tuesday, December 18, 2012 11:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
mozturk 92000 (12/18/2012)
(It's HQL which is similar to T-SQL)

How can I fix my problem ?


Write it as a stored procedure in T-SQL instead of relying on HQL.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1398177
Posted Thursday, December 20, 2012 8:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 7, 2013 8:05 AM
Points: 2, Visits: 3
T-SQL :
Select * from Gecoserv where typeOfEcosystem IN (:typeNameList) AND service IN (:serviceNameList)
Post #1399020
Posted Friday, December 21, 2012 7:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 13,093, Visits: 12,573
mozturk 92000 (12/20/2012)
T-SQL :
Select * from Gecoserv where typeOfEcosystem IN (:typeNameList) AND service IN (:serviceNameList)


That is the same thing you posted originally. It seems you want some help with the code. I am willing to help but you have to provide me with something to work with. ddl, sample data and desired output. As I said before you find details about best practices for posting questions by clicking the first link in my signature. I am certain that if you post the necessary information you will have an answer very quickly.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1399417
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse