Oracle database

DECODE function in oracle

Written by meda

DECODE Function

 DECODE Function is an oracle database conditional function and has more than three parameters. The function returns different values based on the values of the comparison condition parameter.

The syntax for DECODE Function is:

DECODE(expr1,comp1, iftrue1,[comp2,iftrue2…[ compN , iftrueN]], [iffalse])

if the value of the ‘expr1’ and ‘comp1’ parameters are equal, the function returns the value of the ‘iftrue1’ parameter and if the value of ‘expr1’ and ‘comp2’ parameter are equal it also returns the value of the ‘iftrue2’ parameter, and so on.

Here is a simple example on how we use the Function practically in select statement using SQL*PLUS.

---Let's select from tbl_customer to view the contents of the table.

D:\app\Admin\virtual\virtual\product\12.2.0\dbhome_1\bin>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 2 17:17:48 2022

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn mereba/mereba@orcl
Connected.
SQL> col cust_no format a30;
SQL> col customer_type format a10;
SQL> col cust_name format a25;
SQL> col country format a15;
SQL> col swift_code format a12;
SQL> select * from tbl_customer;

CUST_NO CUSTOMER_T CUST_NAME COUNTRY SWIFT_CODE
---------- ---------- ------------------------- --------------- ------------
########## I Messi John Dave USA
########## C Mereba Technology UK
########## B Swiss Bank Switzerland

As you can see in the above query, the customer_type column has values such as ‘I’,’C’ and ‘B’.

And  also select from tbl_customer:

DECODE Function

As you can see also in the above query, ‘I’ is substituted by Individual, ‘C’ is substituted by Corporate and B is also substituted by Bank.