Re: conditional selection of 'AND' or 'Select' in Oracle

"AnaCDent" <anacedent_at_hotmail.com> wrote in message news:SCkgc.55085$U83.54238_at_fed1read03...
| Rohit Dhawan wrote:
| > I have one query similar to the following:-
| >
| > select coursenum,sectionnum,instructor from
| > SECTION s where (sectionnum = '001')
| > and coursenum LIKE 'MATH%'
| >
| >
| > but is it possible to add another 'and' condition to the above query
| > provided a certain user parameter = 'add'
| >
| > for e.g
| >
| > select coursenum,sectionnum,instructor from
| > SECTION s where (sectionnum = '001')
| > and coursenum LIKE 'MATH%'
| > and instructor LIKE 'L%' i.e. only and only if :parameter = 'add' else
| > the above (first)query gets executed.
| >
| > I do not want to use procedure for the above as I think everyting can
| > be done using and,or,not
|
| HOW?
| SQL language does NOT provide any type of conditional statement
| such as IF, THEN, ELSE
|

actually, decode or case can be used for optional predicate criteria -- but you have to be very careful about the impact on performance, as trying to put too much if/then/else logic in a where clause can cause the optimizer to choose a path that is less that optimal (no, i don't have a specific example at this point, but just make sure you always do and explain plan or a trace to determine the effect as you build up additional criteria)

i'll give an example in a minute, but first, for this situation, no such cleverness is needed

the first query and the second query can be combined (presumably some user interface is providing the values for the bind variables):

select

    coursenum,sectionnum,instructor
from

    SECTION s
where

    sectionnum = :snum
and

    coursenum LIKE :cnu || '%'
and

    instructor LIKE :ins || '%'

if the ins bind variable's contents are null, all instructors are matched, so only the sectionnum and coursenum values affect the results if the ins bind variable contains a value, then instructors are also filtered out

if the logic requires checking the contents of a variable to determine what search to perform, you could use a CASE or DECODE in the where clause -- but again, be careful of the affect on performance

as a simple example:

select *
from scott.emp
where
 deptno = :dnum
and
 (
 ename like :ename || '%'
 or
 sal =

    case
    when :ename is null
    then :sval
    else sal
    end
 )
/

but, again, in this particular case, what's issuing the SQL? if you're issuing it from anything other than SQL*Plus, can't the UI tool check the variables and issue the most appropriate SQL statement?

;-{ mcs Received on Sun Apr 18 2004 - 07:52:00 CDT