Home » SQL & PL/SQL » SQL & PL/SQL » help required in sql query
help required in sql query [message #350182] Wed, 24 September 2008 05:24 Go to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
I have front end data search application in which i have around 5 input parameters.These five parameters are mapped to columns of 5 diffrent tables.all tables are linked to each other through some keys.
The user can enter values in any of the input boxes or he may enter values in multiple boxes as well . i have to display the search criteria depending upon the input values.
For example , i have first_name,last_name,pin_code etc input columns. user can search on the bases of individual input values or combinations of them.all columns are mapping to diffrent tables.
Please help in writing query for this scenario. I am struck

Thanks in advance
navneet
Re: help required in sql query [message #350188 is a reply to message #350182] Wed, 24 September 2008 05:45 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
To do it with one query you could do this
select *
from <joined_tables>
where surname = nvl(:input_surname,surname)
	and name = nvl(:input_name,name)
	and ....


It's not very efficient because the CBO can't well determinate the right index to use.

But if you're using a client application to build the sql you can inner join ( in case of and operator ) or outer join ( in case of or operator ) multiple queries involving the search of the required field.

select *
from (
		select *
		from <joined_tables>
		where name = 'Alessandro'
	) join (
		select *
		from <joined_tables>
		where surname = 'Rossi'
	) using ( related_key_column )


Bye Alessandro
Re: help required in sql query [message #350192 is a reply to message #350188] Wed, 24 September 2008 05:50 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Thanks Alessandro

First solution exactly matched to my requirement. Now I m trying to use second solution u provided.

thanks a lot again.

Regards,
Navneet

Re: help required in sql query [message #350243 is a reply to message #350182] Wed, 24 September 2008 08:28 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Just as another option to the question in general, you can dynamically build a where clause based on the input parameters that are actually specified.
Re: help required in sql query [message #350248 is a reply to message #350243] Wed, 24 September 2008 08:47 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
smartin wrote on Wed, 24 September 2008 15:28
Just as another option to the question in general, you can dynamically build a where clause based on the input parameters that are actually specified.


Yes but that kind of query may not use more than one index and it may not be good for performances on a really big table.

Bye Alessandro

Re: help required in sql query [message #350251 is a reply to message #350182] Wed, 24 September 2008 08:58 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
There are pros and cons...a con is that you introduce more queries to be parsed and stored in the shared pool, but the pro is that you submit more targetted and specific queries for parsing.

I've had the approach work well for large tables, but as with a lot of things, it all depends Smile Probably a good idea to try out a couple of solutions and benchmark them.

Previous Topic: SQL help
Next Topic: accessing package in SQL query
Goto Forum:
  


Current Time: Thu Nov 07 16:35:25 CST 2024