Re: DECODE statement: is this a reasonable way to do things?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 3 Jul 2001 09:59:43 -0700
Message-ID: <9hstlv02g4s@drn.newsguy.com>

In article <Xml07.4424$A51.1022535_at_monolith.news.easynet.net>, "Benetnasch" says...
>
>Just wondered whether this is a reasonable way of doing things:
>
>I have a PL/SQL procedure which has, as an argument, a flag.
>If this flag is a 'Y', then I want only records with a value of 17
>in a specific column to be selected. If the flag isn't "Y", then
>all records can be selected.
>
><column name> is a number, so I don't really want to TO_CHAR
>it, as it has an index on it.
>
>My code is thus:
>
> AND <column name> BETWEEN DECODE(p_newflag
> , 'Y', 17
> , 0)
> AND DECODE(p_newflag
> , 'Y', 17
> , 100)
>
>(the column is only NUMBER(2), so 100 should be big enough)
>

well, if the flag is Y I would want to use an index.

if the flag ISN'T Y -- I do not want to use an index (retrieving too many rows from the table to use an index efficiently)

I want two queries. I would use a ref cursor.

   type rc is ref cursor;
   l_cursor rc;
   ...
begin

if ( p_newflag = 'Y' )
then

open l_cursor for select * from t where CNAME = 17; else

open l_cursor for select * from t; end if;

loop

      fetch l_cursor into ...;
      exit when l_cursor%notfound;
      ...

end loop;

close l_cursor;
end;

>This is the way I've thought of doing it, but I'd be grateful if anyone can
>suggest a better way of doing it.
>
>Thanks for any assistance,
>
>--
>Benetnasch
>Remove ".spam.begone"
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Tue Jul 03 2001 - 11:59:43 CDT