Re: tricky SQL Question

From: Greg Stark <greg-spare-1_at_mit.edu>
Date: Sat, 05 Feb 2000 17:30:02 GMT
Message-ID: <8766w35zhp.fsf@HSE-Montreal-ppp33976.qc.sympatico.ca>

izblank_at_yahoo.com (Isaac Blank) writes:

> select product
> from product_lookup
> where keyword in ('wood','bat')
> group by product
> having count(*)=2
>
> If you want to match for more keywords, then change the WHERE and
> HAVING clauses correspondingly

This can be done with parameters too:

where keyword in (:k0,:k1,:k2,:k3,:k4,:k5,:k6,:k7,:k8,:k9) ...
having count(*)=:n

then execute this query with the keywords you want and NULL for the remaining keywords, and :n set to the number you actually passed.

This would allow you to cache the cursor and just execute it repeatedly. This is important if you're processing lots of queries, otherwise Oracle's sql cache becomes useless quickly.

--
greg Received on Sat Feb 05 2000 - 11:30:02 CST