Oracle Decode Function | Use PLSQL DECODE function to handle NULL and default values

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

The DECODE function works as multiple if conditions or CASE statements.
The DECODE function compares one expression to one or more expressions and returns the corresponding result expression.The default value will be returned when there is no match.It allows to transform data values at run time.

In newer versions of Oracle the CASE is used instead of DECODE.

Syntax:

decode( expression , search_1 , result_value1 ,...., search_n , result_valuen , default_value )

At the end of the decode statement is the default value

Example:

Step1: Create a table and insert records as follows

SQL> create table marks(name varchar2(20),grade number,mark number);

Table created

SQL> insert into  marks values ('Chris',1,6);
SQL> insert into  marks values ('James',3,4);
SQL> insert into  marks values ('John',2,5);
SQL> insert into  marks values ('Ram',1,6);
SQL> insert into marks values('Kate', 4,3);

5 rows inserted.

Now let us get the details and based on the number value of the Grade let us get the detailed values.

SQL> select name,mark ,decode(grade,1,'GRADE A',2,'GRADE B',3,'GRADE C', 'Below Average') Grade from marks;
NAME                           MARK     GRADE
Chris                             6     GRADE A
James                             4     GRADE C
John                              4     GRADE B
Ram                               6     GRADE A

In the above example we use the DECODE function to check the grade value and display the Grades in textual form.There is a default value ‘Below Average’ when grades are not 1,2 or 3.

SQL and PLSQL

SQL and PLSQL Technology Tips

Oracle Decode Function | Use PLSQL DECODE function to handle NULL and default values

3 thoughts on “Oracle Decode Function | Use PLSQL DECODE function to handle NULL and default values”

Leave a Reply to Anonymous Cancel reply