Re: case when ... then expression

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