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.
Related Articles,
- Oracle Decode Function | Use PLSQL DECODE function to handle NULL and default values
- SQL: MAX Function | SQL Max examples
- SQL GROUP BY Statement | SQL GROUP BY Clause examples
- NVL | Oracle/PLSQL: NVL Function
- SUBSTR functions | Oracle/PLSQL: Substr Function
- Oracle/PLSQL: Instr Function |INSTR functions |Case sensitive search
- Oracle pl/sql error: ORA-06502: PL/SQL: numeric or value error:
- Oracle pl/sql error: ORA-06512
- ORA-12154: TNS:could not resolve the connect identifier specified
- Oracle timestamp format milliseconds| Oracle Date format
Technorati Tags:
Oracle Decode Function, DECODE function, plsql DECODE
nice post. thanks.
Pingback: COUNT() Function in SQL ,Using COUNT(*) and COUNT(1) in Oracle | SQL and PLSQL
Pingback: Oracle Trim Function | PL/SQL TRIM() Function. | SQL and PLSQL