dynamic statement in where clause???

DeclanB

Hi all,

I am writing a procedure and the query will be different based on the input parameters.

I'm wondering how I can do this inside the where clause? simplified example

select *
from tableA a, tableB b
where a.id = b.id
/* if input parameter = 'abc' then */
and a.total = b.total
/* if input parameter = 'xyz' then */
and a.total != b.total

I was thinking case statement but from what i read it cannot be used in this situation

Thanks

728534

Hi,
Give this a try.
not Tested

select * 
from tableA a, tableB b
where a.id = b.id
and (a.total=
case when :input_param='abc' then
 b.total
end
or
a.total != 
case when :input_param='xyz' then
 b.total
end)

Cheers!!!
Bhushan

Arun Kumar Gupta

Its advisable to use two different select statements using IF ..ELSE .. END IF logic checking the parameter value in IF clause.

Regards
Arun

Centinul

If the number of possible iterations of the input parameter are small I recommend the following (pseudo-code):

IF input_parameter = 'abc' THEN
        SELECT  *
        FROM    TABLEA A
        JOIN    TABLEB B        ON A.ID = B.ID
        WHERE   A.TOTAL = B.TOTAL;
ELSIF input_parameter = 'xyz' THEN
        SELECT  *
        FROM    TABLEA A
        JOIN    TABLEB B        ON A.ID = B.ID
        WHERE   A.TOTAL != B.TOTAL;
END IF;

This allows the query to be simpler and will probably yield better estimates out of the CBO.

728534

Hi,
Give this a try.
not Tested

select * 
from tableA a, tableB b
where a.id = b.id
and (a.total=
case when :input_param='abc' then
 b.total
end
or
a.total != 
case when :input_param='xyz' then
 b.total
end)

Cheers!!!
Bhushan

DeclanB

I've simplified the example quite a bit.

I am already switching on another input parameter so I want to try avoid having the similar query multiple times.

Centinul

DeclanB wrote:
I've simplified the example quite a bit.

Ah. It's always helpful to provide an example that accurately depicts the situation at hand.

I am already switching on another input parameter so I want to try avoid having the similar query multiple times.

It could still be manageable if the number of inputs is small, but you may try the following:

        SELECT  *
        FROM    TABLEA A
        JOIN    TABLEB B        ON A.ID = B.ID
        WHERE   (input_parameter = 'abc' AND A.TOTAL = B.TOTAL)
        OR      (input_parameter = 'xyz' AND A.TOTAL != B.TOTAL);