Learn how to use oracle decode more effectively

May 25, 2011 1:27 pm2 commentsViews: 501

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

2 Comments

  • 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.

  • Thanks Suraj ,,, nice to hear that u like it ,,,Thanku

Leave a Reply



+ 4 = eleven

The Ultimate Herpes Protocol Review

cat pics

Scary Car crashes accident compilation

Most Motivational Video Ever

Wins and Fails Compilation

gay social networking

Cats in Clothes Pics

Huge Vines Compilation

Motorcycle Fails Compilation

Auto binary signals

Blue Eyed Cats Pics

Green Eyed Cats Pics

Abyssinians Cats in Pics

New Vine Compilation

Wet Cats

Healthy diet

Black and White Cats Pics

Amazing VINES Compilation

Funny Vine Compilation

Funny Vines

Most Inspirational Video Ever

Crazy car crashes compilation

Cute Cat Funny Cat Compilation

Muscle Maximizer Review

Baby Laughing Compilation

Funny Cats Compilation

Funny & Cute Cats Compilation

Stunts Fails Compilation

Kitty fights against sleep

Garcinia cambogia reviews

Flying Cats in Pics

Talking Cats

Funny Vine Compilation

Cat Fight in Pics

Garcinia cambogia review

Crazy Vine Compilation

Funny Video

Collection of scary ghost videos

Best Wins and Fails Compilation

Fail Compilation

viral script

Google Sniper 2 Review

Cute Kittens

Cute Kittens Pics

Best Vines

Cute Kittens in Pics

Smiling Cats Pics

Scared Cat Compilation

Best Vines Compilation

Funny Cats Pics Part 1

Funny Animal Videos

Automatic Binary Options Review

Fat Cats in Pics