How to decode in SQL Server 2005

[dan-x]

I am a novice to SQL Server, so this is probably a really easy problem
to fix. I'm translating an Oracle query and need to change the
'decode' to something compatible. Everything I've read points me to
using 'case' but no matter how I write it I can't get it to work and
get a syntax error. Suggestions?

select SYST CTR
, isnull(substring(CD_A, 1, 3), ' ') RESCODE
, DES DESCRIPTION
, decode (substring(CD_A, 1, 3), CODE,PRICE,0) UNIT_PRICE
, count (distinct OR_NO) QTYW

[Ed Murphy]

da***@yahoo.com wrote:

I am a novice to SQL Server, so this is probably a really easy problem
to fix. I'm translating an Oracle query and need to change the
'decode' to something compatible. Everything I've read points me to
using 'case' but no matter how I write it I can't get it to work and
get a syntax error. Suggestions?

select SYST CTR
, isnull(substring(CD_A, 1, 3), ' ') RESCODE
, DES DESCRIPTION
, decode (substring(CD_A, 1, 3), CODE,PRICE,0) UNIT_PRICE
, count (distinct OR_NO) QTYW

*googles (Oracle "decode function")*

select SYST CTR
, isnull(substring(CD_A, 1, 3), ' ') RESCODE
, DES DESCRIPTION
, case substring(CD_A, 1, 3)
when CODE then PRICE
else 0
end UNIT_PRICE
, count (distinct OR_NO) QTYW

[dan-x]

select SYST CTR

, isnull(substring(CD_A, 1, 3), ' ') RESCODE
, DES DESCRIPTION
, case substring(CD_A, 1, 3)
* when CODE then PRICE
* else 0
end UNIT_PRICE
, count (distinct OR_NO) QTYW

Thank you. That worked great!

[--CELKO--]

Oracle now has the CASE expression, too.

[DA Morgan]

--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
da******@x.washington.edu (replace x with u to respond)

[David Portas]

"DA Morgan" <da******@psoug.orgwrote in message
news:12***************@bubbleator.drizzle.com...

--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
da******@x.washington.edu (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?

--
David Portas

[DA Morgan]

David Portas wrote:

"DA Morgan" <da******@psoug.orgwrote in message
news:12***************@bubbleator.drizzle.com...

>--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
da******@x.washington.edu (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>

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

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
da******@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[David Portas]

"DA Morgan" <da******@psoug.orgwrote in message
news:12***************@bubbleator.drizzle.com...

David Portas wrote:

>"DA Morgan" <da******@psoug.orgwrote in message
news:12***************@bubbleator.drizzle.com.. .

>>--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
da******@x.washington.edu (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
da******@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Erland Sommarskog]

DA Morgan (da******@psoug.org) writes:

The main reason is that we use what we know best.

That's probably why the syntax with the JOIN operators have been so
endorsed in the SQL Server community. Looking back, I can clearly
say that never really understood how *= worked. Yeah, I used it, but
it was always fragile. The ANSI syntax is such a great relief.

Apparently += in Oracle is more cleanly defined.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DA Morgan]

David Portas wrote:

"DA Morgan" <da******@psoug.orgwrote in message
news:12***************@bubbleator.drizzle.com...

>David Portas wrote:

>>"DA Morgan" <da******@psoug.orgwrote in message
news:12***************@bubbleator.drizzle.com. ..
--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
da******@x.washington.edu (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.

We do but the reality is that when they hit the marketplace, in an
Oracle shop, they will find 99.9% of the code with the old format so
they have to know how to read it. And a lot of places having internal
coding standards that they must comply with.

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.

Using the standard for a product is hardly obfuscation. And the number
of people using those other products is surprisingly smaller than you
think (provided you exclude "users" who never see a line of code). The
only two widely deployed commercial RDBMS platforms for development are
Oracle and SQL Server. The others are niche and/or legacy.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
da******@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Erland Sommarskog]

DA Morgan (da******@psoug.org) writes:

Erland Sommarskog wrote:

>Apparently += in Oracle is more cleanly defined.

Excuse me ... but Oracle doesn't have any *= or += syntax.

Did I ever say that I knew Oracle? :-)

If you think one is clearer than the other it is only
because it is the one you know best. I find them both
equal in all respects until you get to the minor players
such as the CROSS JOIN, FULL OUTER JOIN, and the totally
stupid NATURAL JOIN.

Good. *= and LEFT JOIN is definitely not equal in SQL Server.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ed Murphy]

DA Morgan wrote:

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>

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

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.

The /other/ other argument is that, once your query gets a little less
trivial than the above, JOIN puts the join conditions closer to the
tables involved, making it easier to understand how they're joined and
harder to introduce an unintended cross join by omitting a condition.