A beautiful rejection of the "Bang!.... Stop or I'll shoot" DBA
mentality....
--
==============================
Connor McDonald
http://www.oracledba.co.uk
"Some days you're the pigeon, some days you're the statue..."
"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
news:9h9rn202maf_at_drn.newsguy.com...
> 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
>
Received on Tue Jun 26 2001 - 16:42:42 CDT
