Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic queryin based on number of inputs provided
Dynamic queryin based on number of inputs provided [message #376057] Mon, 15 December 2008 19:01 Go to next message
coolguy01
Messages: 64
Registered: August 2006
Member
I have 7 columns in a tables for which the user can put in values in the fields in the front end and query. The 7 columns have 7 corresponding fields in the front end. But the querying has to be dynamic ... i.e the user does not have to enter all the items in the front end. He can just enter one column value, or 2 upto 7 values for the different columns and it should pass these values to the back end and query the table based on the user inputs and retrieve the rows.

This means while doin the select on the table the "where" clause where the user provides inputs should be flexible. It should just filter the data based on how many ever input fields the user types in.
Please let me know how I can achieve this.

For Example if the columns are col1,col2,col3 .....col7

If the user just provides value for col1 = col1value and col3 = col3val from front end the query should be like
select * from table where col1= col1value and col3 = col3value

similarly if he just provides col2= col2val and col3 = col3 val then
select * from table where col2= col2value and col3 = col3value
Re: Dynamic queryin based on number of inputs provided [message #376059 is a reply to message #376057] Mon, 15 December 2008 19:07 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
qurry through all_tab_columns
yours
dr.s.raghunathan
Re: Dynamic queryin based on number of inputs provided [message #376061 is a reply to message #376057] Mon, 15 December 2008 21:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
Put all 7 parameters in your query and use nvl to handle the null values:

select * from table
where col1 = nvl (col1value, col1)
and col2 = nvl (col2value, col2)
and col3 = nvl (col4value, col3)
and col4 = nvl (col4value, col4)
and col5 = nvl (col5value, col5)
and col6 = nvl (col6value, col6)
and col7 = nvl (col7value, col7);



Re: Dynamic queryin based on number of inputs provided [message #376078 is a reply to message #376061] Mon, 15 December 2008 23:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
...or better even if longer to write:

select * from table
where (col1value is null or col1 = col1value)
and (col2value is null or col2 = col2value)
and (col3value is null or col3 = col3value)
and (col4value is null or col4 = col4value)
and (col5value is null or col5 = col5value)
and (col6value is null or col6 = col6value)
and (col7value is null or col7 = col7value);

Regards
Michel
Re: Dynamic queryin based on number of inputs provided [message #376103 is a reply to message #376078] Tue, 16 December 2008 01:25 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
For curiosity, why ist this syntax better then the one from @Barbara?
Re: Dynamic queryin based on number of inputs provided [message #376112 is a reply to message #376103] Tue, 16 December 2008 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First, it clearly shows what it means.
Then, exception for the latest versions, for the optimizer NVL is a function like any other ones with their disadvantages on index usage.

Now in 10.2.0.4 and 11g, with some indexes, it can be more efficient with NVL because optimizer knows the meaning of this function and can analyze what is inside (the form col=nvl(value,col) is so often used that it has been included in the optimizer knowledge).

In addition, both queries are wrong for row containing NULL values.

Regards
Michel
Re: Dynamic queryin based on number of inputs provided [message #376232 is a reply to message #376112] Tue, 16 December 2008 09:23 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

In addition, both queries are wrong for row containing NULL values.




SELECT *
   FROM TEST_TBL
WHERE
 ( col1value IS NULL
OR ( col1value  IS NOT NULL
AND col1         = col1value))
AND ( col2value IS NULL
OR ( col2value  IS NOT NULL
AND col2         = col2value))
AND ( col3value IS NULL
OR ( col3value  IS NOT NULL
AND col3         = col3value))
AND ( col4value IS NULL
OR ( col4value  IS NOT NULL
AND col4         = col4value))

...



[Updated on: Tue, 16 December 2008 09:26]

Report message to a moderator

Re: Dynamic queryin based on number of inputs provided [message #376239 is a reply to message #376232] Tue, 16 December 2008 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Your query is equivalent to my previous one.

Depending on what result OP wants when a column contains a null value, the query should be modified or not.

Regards
Michel

[Updated on: Tue, 16 December 2008 10:02]

Report message to a moderator

Re: Dynamic queryin based on number of inputs provided [message #376268 is a reply to message #376239] Tue, 16 December 2008 13:58 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Looks euivalent but have differences...

My answer is if passing parameter value is null,your query will return no rows but the query I have written in previous post will return all rows. If passing parameter value is not null then it will fetch the rows based on the passing value.Thats what the difference is..


Regards,
Olivia

[Updated on: Tue, 16 December 2008 14:01]

Report message to a moderator

Re: Dynamic queryin based on number of inputs provided [message #376280 is a reply to message #376268] Tue, 16 December 2008 16:36 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I've done one of these and it worked well, but you need to understand data type conversion well and make the effort to do it right. Overkill for most folks.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279

FYI, for an inefficient alternative that avoids the null/not null issue described, you can do this for character fields (similar to a like search). This is straight out of Apex. ":P1_REPORT_SEARCH" is a bind variable containing your search term (google style).

select 
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
 from   "EMP" 
where 
(   
 instr(upper("ENAME"),upper(nvl(:P1_REPORT_SEARCH,"ENAME"))) > 0  or
 instr(upper("JOB"),upper(nvl(:P1_REPORT_SEARCH,"JOB"))) > 0 
)


(Added correct link)

[Updated on: Tue, 16 December 2008 16:48]

Report message to a moderator

Re: Dynamic queryin based on number of inputs provided [message #376340 is a reply to message #376268] Wed, 17 December 2008 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
My answer is if passing parameter value is null,your query will return no rows but the query I have written in previous post will return all rows

Wrong, both previous queries return all rows.

Quote:
If passing parameter value is not null then it will fetch the rows based on the passing value

Exactly the same thing for other queries.

Regards
Michel
Re: Dynamic queryin based on number of inputs provided [message #376364 is a reply to message #376239] Wed, 17 December 2008 02:20 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
What does this OP stands for?

regards,
Delna
Re: Dynamic queryin based on number of inputs provided [message #376366 is a reply to message #376364] Wed, 17 December 2008 02:23 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
i assume original poster
Re: Dynamic queryin based on number of inputs provided [message #376369 is a reply to message #376364] Wed, 17 December 2008 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, OP is Original Poster, it is the only acronym that is allowed here as it is widely used in every forum.

Regards
Michel
Re: Dynamic queryin based on number of inputs provided [message #376372 is a reply to message #376369] Wed, 17 December 2008 02:48 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks sir(s),

regards,
Delna
Previous Topic: query regarding DROP TABLE
Next Topic: Please help me to solve this query
Goto Forum:
  


Current Time: Wed Nov 13 05:11:14 CST 2024