Parameters in SQL query

[hilluly]

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.

Thanks in advance for your help

[ales]

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.

Hope It's not confusing.
Ales

[hilluly]

Hey Ales,
thanks for the reply, would I use the decode statement to test for the parameter, if not could you give me an example.

Thanks
Ulysses

[ales]

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);
END IF;

IF p_in!=0 THEN
WC:=WC||' AND CN='||to_char(p_in);
END IF;

IF p_tn!=0 THEN
WC:=WC||' AND CN='||to_char(p_tn);
END IF;

IF LENGTH(WC)>0 THEN
WC:=SUBSTR(WC,5); --cut off first AND
WC:='WHERE'||WC;
END IF;
FQUERY:='SELECT * FROM T '||WC;
--then use dbms_sql for opening cursor ...

If you need more info, just write.
Ales

[ales]

Of course, I made silly mistake (hope only this one):

IF p_cn!=0 THEN
WC:=WC||' AND CN='||to_char(p_cn);
END IF;

IF p_in!=0 THEN
WC:=WC||' AND IN='||to_char(p_in);
END IF;

IF p_tn!=0 THEN
WC:=WC||' AND TN='||to_char(p_tn);
END IF;

Ales

[hilluly]

Is it possible to accomplish this through a SQL query statement?//

[chrisrlong]

You 'can', but the dynamic SQL will perform better.

If :c1 is required, but :c2 and :c3 are optional, you can do this:


SELECT
----*
FROM
----T
WHERE
----T.C1----=----:C1----------------AND
----T.C2----=----NVL(:C2,T.C2)----AND
----T.C3----=----NVL(:C3,T.C3)

HTH,

- Chris

[hilluly]

Thanks for all the help Ales and Chris.

Ulysses