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.

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

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.

No related posts.