I have a query where a user can enter up to three parameters(search conditions), but the user may not enter all three every time they query is called, but they will have to enter at least one of the parameters. For example, I have a query that selects from a table based on a contract number, invoice number, and transaction number. The user can has to enter the invoice number, but the other two are optional(for purposes of narrowing the data returned).
My question is how do I set my query up to run and ignore(??) the parameters that are not required to be entered?// I hope I have made sense with my question.
Good method is sequential creation of WHERE clause (and whole query, of course) as character string:
Start with empty string.
Test each parameter and if given, add string ' AND column=value_of_parameter '
If final string is not empty, replace the first 'AND' with 'WHERE'
Put it to the rest of query.
Then send it to Oracle.
It's up to you ... if you choose DECODE, you can use it, but bear in mind it's usable only in a SQL statement.
You must know whether user filled a parameter, so you must have a value of parameter indicating "not given", for example:
(you didn't mention a language, I'll use PL/SQL)
Suppose we have table T with these columns of NUMBER type:
CN = contract number
IN = invoice number
TN = transaction number
Assume no one of them can be 0 (zero), then we can choose 0 as value for not-given-parameter.
Parameters are p_cn, p_in and p_tn and they are comming in this code from a user_interface. If a user filled a parameter, it doesn't contain 0.
WC:='' --means WhereClause :-)
IF p_cn!=0 THEN
WC:=WC||' AND CN='||to_char(p_cn);
IF p_in!=0 THEN
WC:=WC||' AND CN='||to_char(p_in);
IF p_tn!=0 THEN
WC:=WC||' AND CN='||to_char(p_tn);
IF LENGTH(WC)>0 THEN
WC:=SUBSTR(WC,5); --cut off first AND
FQUERY:='SELECT * FROM T '||WC;
--then use dbms_sql for opening cursor ...