• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » How to use Decode in Oracle

How to use Decode in Oracle

November 5, 2016 by techgoeasy Leave a Comment


Table of Contents

  • Introduction to Oracle sql decode
  • Some More points to remember for Oracle Decode
  • How to read decode in Oracle
  • FAQ about Oracle sql Decode processing

Introduction to Oracle sql decode

In this section we will discuss about Oracle decode processing which is very important aspect in Oracle sql statement

Oracle  decode is the method in Oracle database to  transform data values for one value to another which is better to understand.Oracle Decode transform data values at retrieval time.

Oracle decode

It is a type of If then else for the processing

The code block for Oracle sql Decode is below

decode(expression or column name, match, result [,match, result]…[,default] )

Here are the meaning for terms in above code
a) expression or column  is the value to compare
b) match is the value that is compared against expression
c) result is the value returned, if expression is equal to match
d) default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return NULL (no matches found).

Example

 

select
decode (
Phase Code,
‘P’,’Pending’,
‘C’,’Completed’,
‘T’,’Terminated’,
‘S’,’Standby’,
‘UNKNOWN’
)
from
FND_REQUESTS;

 

Here is the algorithm to better understand it

1) Oracle retrieve  the column value of Phase code
2) if Phase code = ‘P’  then Pending
3) if Phase code = ‘C’  then Completed
4) if Phase code = ‘T’  then Terminated
5) if Phase code = ‘S’  then Standby
6) If Phase code is neither of the above ,the decode returns Unknown
7) If default is not present it will give null

Note that Oracle decode starts by specifying the column name or expression , followed by set of matched-pairs of transformation values. At the end of the decode statement we find a default value. The default value tells decode what to display if a column values is not in the paired list.

We could say the algorithm like this way

if (expr == search1)
return(result1);
elseif (expr == search2)
return(result2);
…elseif (expr == searchn)
return(resultn);
else
return(default);

 

Some More points to remember for Oracle Decode

1)In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.

SQL> SELECT decode(null,null,1,0) FROM dual;
DECODE(NULL,NULL,1,0)
———————
1

2) The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.

3)Oracle automatically converts the values for expression and compare_value to the datatype of the first compare_value. Also the datatype of the return_value is converted to the datatype of the first return_value. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2

How to read decode in Oracle

we can read the decode statement as if else if statement. The first argument in the decode statement will be generally some column where data transformation is needed. The argument after that will be comparing the values of first argument with it

FAQ about Oracle sql Decode processing

(1)We have seen that “expr” being equated to specified values,Can we use inequality operator like > or <  ?

Lets take an example
SELECT DECODE(salary,< 50000, sal + 1000, sal + 500) Final_salary FROM emp;
ERROR at line 2
ORA-00936: missing expression
So we cannot use that. We need to use case statement to do it.  or we can sign function in decode  to achieve it
SELECT DECODE(sign(salary- 50000), -1,sal + 1000, sal + 500) Final_salary FROM emp;
Basically we need to convert our requirement to some formula which can evaluate to some value

(2)How to compare two column values using oracle decode?

SELECT col1,col2 decode( abs(col1-col2), 0, ‘col1 = col2’,Col1-col2, ‘col1 > col2′,’col1 < col2’)
FROM example_tab;

(3) What is the difference between Decode and CASE

CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.
CASE expects datatype consistency, DECODE does not
CASE expects datatype consistency, DECODE does not
DECODE can work with only scalar values but CASE can work with logical operators, predicates and searchable subqueries.

(4) Do oracle decode have a limit

Yes The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.

Related links
Oracle documentation on decode
how to write sql queries
Basic Sql statement
Oracle Case Statement Explained with Tips and Examples
SQL tutorial


Filed Under: Oracle, Oracle Sql Tagged With: oracle decode, Oracle sql decode, Oracle sql decode processing

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • Oracle Indexes and types of indexes in oracle with example
  • Top 30 Most Useful Concurrent Manager Queries
  • Top 46 Oracle ASM Interview Questions
  • Oracle dba interview questions and answers
  • 40 question you must know about R12.2



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Multi Language Support in 11i/R12
  • EBS PDB service name disappear from listener in 19c
  • how to download oracle e-business suite 12.2 software
  • Step by step upgrade process to R12.2 Upgrade part -2(Main Upgrade Driver for R12.2.0)
  • Step by step upgrade process for R12.2 Upgrade Part -1

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us