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

In article <d01b518e.0107060424.12798998_at_posting.google.com>, peter_at_fluxx.com says...
>
>Hello ...
>
>what do you think about
>
>SELECT * FROM xtab
>WHERE <column_name> = DECODE(p_newflag, 'Y', 17, <column_name>)
>
>With this technique the length of the column_name does´nt matter ...
>

it will preclude an index on the column <column_name> when p_newflag is 'Y'.

A ref cursor would be much more appropriate in this case:

...
is

    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

in this case, the optimizer will have one of two queries to optimize -- the first one can make use of indexes, the second will full scan (as they most likely should)

>Greetings
>Manfred Peter
>(Alligator Company)
>www.alligatorsql.com

--
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