web analytics
Oracle PL/SQL: IF-THEN-ELSIF Statement vs. Case statement , CASE Expression vs. Decode Function Options
codeling
Posted: Wednesday, May 17, 2017 10:57:04 AM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 832
Points: 3483

Can Decode function return different data types?

The Decode function can't return different data types, so the following usage of decode function will report error:

decode(table.col1,value1,1001,
                  value2,6006,
       'no value')

You can rewrite the code by using the to_char() function:

decode(table.col1,value1,to_char(10),
                  value2,to_char(60),
       'no value')
Sponsor
Posted: Wednesday, May 17, 2017 10:57:04 AM
codeling
Posted: Wednesday, May 17, 2017 2:00:33 PM

Rank:Advanced Member
Groups: Member
Joined: 12/11/2015
Posts: 832
Points: 3483

You can also use COALESCE as a variety of the CASE expression. For example,

COALESCE (expr1, expr2)

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Similarly,

COALESCE (expr1, expr2, ..., exprn), for n>=3

is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE (expr2, ..., exprn) END

COALESCE returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null.

Oracle Database uses short-circuit evaluation. That is, the database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

If all occurrences of expr are numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

Users browsing this topic
Guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.


© 2017 Digcode.com. All rights reserved.