Re: How to decode in SQL Server 2005

"DA Morgan" <damorgan@xxxxxxxxx> wrote in message
news:1204984319.122228@xxxxxxxxxxxxxxxxxxxxxxxxx
David Portas wrote:
"DA Morgan" <damorgan@xxxxxxxxx> wrote in message
news:1204868296.573578@xxxxxxxxxxxxxxxxxxxxxxxxx
--CELKO-- wrote:
Oracle now has the CASE expression, too.
Since version 9.01 which is more than 7 years old.
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)


Exactly. Yet I still see Oracle developers today using the legacy
non-standard, non-portable features such as the (+) join syntax, DECODE,
NVL (to name just a few) even though Oracle has long since supported the
ISO standard alternatives.

Oracle implemented SQL92 features much later than IBM, Microsoft or
Sybase so I suppose that's one reason why the standard seems less widely
adopted on Oracle. Are there other reasons why some Oracle developers
aren't using standard SQL?

The main reason is that we use what we know best. Most of us
learned it, it works, and there are no advantages to the change.
The second reason is that the initial implementation 7-8 years
ago had a small bug or two that decreased any initial appeal.

I teach SQL and speaking for myself only use ANSI when I am
doing something that "requires" it such as a full join as some
of the syntax is just plain idiocy (natural join) and the changes
provide no substance.

SELECT COUNT(*)
FROM t1, t2
WHERE t1.c1 = t2.c1;

Ok replace the comma with the word JOIN. (+3 bytes)
Replace the word WHERE with ON. (-3 bytes)
Net change in bytes 0
Net change in functionality 0
Are we getting excited yet? <g>


But your example query already IS standard in ANSI/ISO 92/99/2003 syntax so
this doesn't advance any argument against the standard.

With outer joins using (+) you get to delay the onset of carpal
tunnel syndrome by a fraction of a second.


Hmm... I think you were right the first time though. People use it because
that's what they know, perhaps without even realising the alternatives. I
would hope that teachers would take the time to educate people about the
newer standard syntax rather than sticking purely or mainly to the old
cliquey ways though. There are millions of people using other database
products who are more familiar with the standard syntax. Even if you never
have to port that's no excuse for obfuscating code that others may inherit
one day.


The other argument you allude to is portability. And I'm sorry
to say it but that argument, from an Oracle perspective, is nonsense.
The only code that is truly portable is also truly mediocre.

To make Oracle code portable to DB2, Informix, or Sybase would
be a wasted effort as these products are marginalized to niche
markets. To make it portable to SQL Server would require not
using more than 50% of Oracle's functionality. I can understand
why Microsoft might make that argument but it will fall on deaf
ears in an Oracle shop with good reason. Give up regular expressions
because another product doesn't have them? Why? Give up before
triggers because another product doesn't have them? The list is very
long.

But back to the original topic ... CASE was quickly adopted, and the
reason is that it provided new and advantageous functionality over
the older decode.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org


.

Relevant Pages

  • Re: How to decode in SQL Server 2005
    ... Yet I still see Oracle developers today using the legacy non-standard, non-portable features such as the join syntax, DECODE, NVL even though Oracle has long since supported the ISO standard alternatives. ... Oracle implemented SQL92 features much later than IBM, Microsoft or Sybase so I suppose that's one reason why the standard seems less widely adopted on Oracle. ... Net change in functionality 0 ...
    (comp.databases.ms-sqlserver)
  • Re: How to decode in SQL Server 2005
    ... Yet I still see Oracle developers today using the legacy ... non-standard, non-portable features such as the join syntax, DECODE, NVL ... so I suppose that's one reason why the standard seems less widely adopted on ...
    (comp.databases.ms-sqlserver)
  • Re: Yet another newbie question
    ... Is my syntax correct for what I'm aiming to do? ... And the syntax I quoted is from Sybase, which is where I acquired it. ... blame Oracle for not following Sybase extensions by saying Oracle is ... That is standard Ansi syntax. ...
    (comp.databases.oracle.misc)
  • Problems connecting to Oracle DB on SLES
    ... Attempting to discover Oracle OCI build rules ... ORA-01756: quoted string not properly terminated (DBD ERROR: OCISessionBegin)) ... all skipped: no reason given ...
    (perl.dbi.users)
  • Re: Is OpenVMS certified yet for 64-way Hard partitions (GS1280 M64).
    ... Is OpenVMS certified yet for 64-way Hard ... Gee - I almost wonder if Oracle didn't sabotage the database so ... Absolutely no reason to do 8.2 vs 8.2-1, so I do not know what your ... Cerner refused to certify RAC. ...
    (comp.os.vms)