Re: case when ... then expression

Stephen Bell wrote:

> Cheers Thomas!
> No one could have said it better!
>
> Thomas Kyte wrote:
>
> > In article <3B3823B5.AD60DF23_at_exesolutions.com>, "Daniel says...
> > >
> > ><comments interspersed>
> > >
> > >Jonathan Lewis wrote:
> > >
> > >> First I don't think you can do:
> > >> m_variable := decode(....);
> > >> and that may have been the requirement.
> > >
> > >Of course you can. That is the basic format of the function. Perhaps you
> > >should examine PL/SQL.
> > >
> >
> > Or maybe, just maybe, you should try your examples first:
> >
> > ops$tkyte_at_ORA8I.WORLD> declare
> > 2 my_var varchar2(10);
> > 3 begin
> > 4 my_var := decode( 1, 1, 'Yes', 'No' );
> > 5 end;
> > 6 /
> > declare
> > *
> > ERROR at line 1:
> > ORA-06550: line 4, column 12:
> > PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement
> > only
> > ORA-06550: line 4, column 2:
> > PL/SQL: Statement ignored
> >
> > ops$tkyte_at_ORA8I.WORLD>
> > ops$tkyte_at_ORA8I.WORLD> declare
> > 2 my_var varchar2(10);
> > 3 begin
> > 4 select decode( 1, 1, 'Yes', 'No' ) into my_var from dual;
> > 5 end;
> > 6 /
> >
> > PL/SQL procedure successfully completed.
> >
> > You have never been able to assign decode to a variable in PLSQL. The error
> > message is pretty self explanatory on that one.
> >
> > >> Second it's a pain in the backside using
> > >> decode() with range tests, as required by
> > >> the original post.
> > >
> > >You're right. Really painful to type <=.
> >
> > extremely painful, lots of nested, unreadable decodes with tricks using the SIGN
> > builtin function -- virtually impossible to do ranges of strings.
> >
> > >
> > >If you don't even know the basic structure of DECODE, see above, exactly
> > >how can you comment on using it with ranges?
> > >
> >
> > I believe you'll find Jonathan has forgotten more about Oracle then most people
> > ever learn in the first place and still retains more then enough to be on top.
> >
> > >> Third
> > >> CASE
> > >> is new, improved, fresh out of the box,
> > >> technology ;)
> > >
> > >Which does absolutely nothing not done by DECODE. But it might also
> > >interest you that CASE has been around in database languages I've worked
> > >with for more than 20 years.
> > >
> > >Please don't follow-up. The fact that you didn't know the basic syntax
> > >for the function pretty much answered any question I might have had.
> > >
> >
> > does that mean you should stop posting now?
> >
> > >Daniel A. Morgan
> > >
> >
> > --
> > 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

Unfortuantely I have to agree. I was doing this while distracted and totally blew it.

My apology to all.

Daniel A. Morgan Received on Tue Jun 26 2001 - 12:38:33 CDT