Learn how to use oracle decode more effectively

Oracle decode function for SQL is another perfect mean to manipulate data. It is the function which helps manage below with ease

Handles NULL VALUES, though it can also be done with NVL, NVL2.

Work as normal IF–THEN–ELSE code block.

Decode provides high readability compare to normal IF–THEN–ELSE statement.

 

Syntax: Oracle Decode Function 


DECODE (::EXPR::, Search, Result [, Search, Result]... [, Default])

DECODE ( EXPR_COLUMN,
If_String_1_avlble, Display_Result_1,  
If_String_2_avlble, Display_Result_2, 
If_String_3_avlble, Display_Result_3,
Default) 

 

Few Sample Scenarios:

 

You can use decode if you want to replace NULL values in “Sales compensation” column as ZERO. Though, we can achieve this either by NVL / NVL2.

Data Masking, Use decode to mask the business data which is supposed to be given to vendor IT team for application testing purpose.  instead of new york you can place NNN.

You can display master data of transactional record. Say example you have kept region of records as keys in data base, example 1 for North America, 2 for Europe and 3 for Asia. you Use decode to replace 1, 2 and 3 for corresponding regions While displaying.

More Technically, oracle decode function compares expression to each search value one after another.
 

 

Few important Observations for oracle decode function:

 
  1. Default:  Returns NULL if default is omitted and expression is not matched with any of the search value.
  2. DATATYPE:Have you ever tried giving multi datatype values in decode?? Yes it accepts but end up with some performance overhead as Oracle tend to convert all the values to most preferred datatype (datatype of first result).
  3. NULL Comprison:Is NULL = NULL ?? We all know that NULL cannot be compared with NULL and both will not to be considered equal. What if I have a specific action to apply for all NULL valued data? you got it – one way to achieve it is decode. Decode considers NULL = NULL.

Technically, oracle function compares expression to each search value one after another.

Example: Decode Oracle SQL

 

 

Partner ID

Partner code

City

100

WM

Newyork

101

CIG

 

102

BSN

India

103

MCD

Newyork

104

PZH

Toronto

105

HPW

PaloAlto

To get the number of partner operating in any city.

 

Select decode(City, null, ’NULL’, City) “Operating City”, count(1) From partners Group by city

 

Result will look like.

Operating City

Count(1)

Newyork

2

India

1

Toronto

1

PaloAlto

1

NULL

1

 

 

 


 

Oracle Decode Function Examples and Syntax

Comments

  1. Suraj says

    Mahender, Nice explanation of oracle Decode. Really what you have explained in this blog are very helpful to the developers. Keep blogging with other important topics like Pushdown Optimization in Infromatica & Partitioning with step by step.

    God bless you………… Thanks again.

Leave a Reply

Your email address will not be published. Required fields are marked *


seven − 1 =