Re: multiple runs with the same ref cursor?

"Billy" <vslabs_at_onwe.co.za> wrote in message news:1123046775.946507.72760_at_g14g2000cwa.googlegroups.com...
>
> jimi_xyz_at_hotmail.com wrote:
>> Ok iv'e been working on this problem for some time now; and haven't
>> really figured anything out. I need to run my ref cursor multiple
>> times, depending on how many of the check boxes the user selects. Let
>> me example, I have a form which has four check boxes, with a text box.
>> The user can either search in the title, approach, scope, or objective;
>> the user also has the option to search all the fields, three of the
>> fields; basically any combination.
>
> You can use IF-THEN-ELSE logic in the predicate to do this.
>
> E.g.
> SELECT
> *
> FROM table t
> WHERE DECODE( :title, NULL, 'N.A', t.title ) = NVL( :title, 'N.A' )
> AND DECODE( :scope, NULL, 'N.A', t.scope ) = NVL( :scope, 'N.A' )
> .. etc ..
>
> The DECODE decodes into :
> IF :title is NULL THEN
> predicate is [ 'N.A' = 'N.A') ]
> ELSE
> predicate is [ table.title = :title ]
> END IF
>
>
> The advantage is that this is a single generic SQL supporting any
> number of filter conditions. There could however be performance
> considerations.
>
> The alternative is dynamic SQL which in turns lead to a number of SQL
> for the various filter combinations. This impacts the shared pool.
>
> --
> Billy
>

the disadvantage to this approach (which I've used on occasion) can be performance problem -- the queries won't use any indexes

better to use dynamic sql or multiple specific sql statements

of course, if you only dealing with VARCHAR2 datatypes, you can simple append a wildcard and use the LIKE operator

++ mcs Received on Wed Aug 03 2005 - 05:43:01 CDT