WHERE CLAUSE [message #22182] |
Fri, 27 September 2002 06:20 |
Gopal L Bairwa
Messages: 2 Registered: September 2002
|
Junior Member |
|
|
Hi,
Depending upon some parameters , where clause for my query changes, how can I handle this in single query.
For example,
if ( Site is selected )
select * from meter where
id in ( select meteridfk from meter_site_link where siteidfk = 10 )
If Customer is selected ( Select all belonging to site )
select * from meter where
id in ( select meteridfk from meter_site_link where siteidfk in ( select id from site where customeridfk=5 )
if none selected ( Selected everything )
select * from meter
|
|
|
Re: WHERE CLAUSE [message #22187 is a reply to message #22182] |
Fri, 27 September 2002 07:36 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
I'm not sure its the most efficient way to do it, but I think this will work:
select * from meter where id in (select meteridfk from meter_site_link where siteidfk = nvl(:p_siteid, -9999))
union
select * from meter where
id in ( select meteridfk from meter_site_link where siteidfk in ( select id from site where customeridfk=nvl(:p_customerid, -9999)))
union
select * from meter where nvl(:p_siteid, -9999) = -9999 and nvl(:p_customerid, -9999) = -9999;
|
|
|
Re: WHERE CLAUSE [message #22189 is a reply to message #22182] |
Fri, 27 September 2002 08:41 |
Gopal L Bairwa
Messages: 2 Registered: September 2002
|
Junior Member |
|
|
Jon,
Thanks for your reply. This is definitely a answer but it has to execute all the three queries all the time,
which you already pointed out that it may not be the most efficient way.
I am looking whether there is any mechanism in SQL which can allow us to select where cluase depending on some condtions.
|
|
|