Dynamic queryin based on number of inputs provided [message #376057] |
Mon, 15 December 2008 19:01 |
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 #376112 is a reply to message #376103] |
Tue, 16 December 2008 01:57 |
|
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 |
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 #376280 is a reply to message #376268] |
Tue, 16 December 2008 16:36 |
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
|
|
|
|
|
|
|
|