Re: Variable where clause

rnarayan_at_tonto.advtech.uswest.com (R. Narayanan) writes:

> Hi,
 

> I am new to Oracle and had been working on Ingres previously.
 

> There is something called as a 'Qualification' clause in Ingres 4GL. I can
> specify a query like this.
 

> Select emp_no, emp_name, addr
> from employee
> qualification
> emp_no := :empno_var
> emp_age := :empage_var
> emp_experience := :emp_experience_var
 

> Note that emp_no, emp_age, emp_experience are columns in the table employee,
> and the corresponding _var's are local program variables.
 

> When Ingres executes this query, if any of these local variables does not
> have any value, then that line does not make a part of the where clause of
> the query executed.
 

> I am trying to find a way of achieving the same in Oracle Stored Procedures,
> written in PL-SQL. I want to write stored procedures which will select
> values from a table, and return the columns. The parameters passed to the
> stored procedure will determine the where clause of the select query. I
> want to omit that value from the where clause, if no value is passed in the
> input parameter.

The SQL that you embed inside your PL/SQL code should look something like this:

Select emp_no, emp_name, addr
from employee
where

        ...
        emp_no like nvl (:empno_var, '%') and
        emp_age like nvl (:empage_var, '%') and
        emp_experience like nvl (:emp_experience_var, '%')

You might want to have a few nvl functions on the left-hand-sides as well, depending on exactly how you want null values to behave.

> Please post a reply or mail to me.
 

> Thanx in advance

You're welcome

> -Nara
 

> ***************************************************************************
> R. Narayanan Home -
> US West Technologies, 1966, S. University Blvd.
> 4001, Discovery Dr. Apt# 2C
> Boulder, CO. Denver, CO-80210
> Ph - 303 541 7056 (Boulder) Ph - 303 765 0480
> 303 624 1114 (Denver)
> Shall be working at Boulder for another couple of months
> ***************************************************************************
> All views expressed are my own etc.

Hank Robinson
Oracle DBA
University of Warwick Received on Mon Aug 29 1994 - 15:27:33 CEST