Decode function vs Case statement in PL-SQL

Decode function vs Case statement in PL-SQL  – Learn how to use Decode function and Case statements in the situations where you need to use  decision making statements with IF -THEN – ELSE logic  with example programs.

DECODE and CASE both provides IF-THEN-ELSE functionality in Oracle SQL. i.e,

IF [CONDITION 1 IS TRUE] THEN

ELSIF [CONDITION 2 IS TRUE] THEN

ELSIF [CONDITION 3 IS TRUE] THEN

......

END IF;

here condition 1 , condition 2 .. are  the expression that evaluates to true or false with some comparison operators like , <,>,=,<=,>=,!=, IS null, is not null .. etc.

Decode

• Decode Function is used to compare values in the decode function and returns the result that match with comparison value.
• Decode statement can be used with only SQL DML statements like SELECT, INSERT, UPDATE,  DELETE.
• DECODE result type is first decoded expression type, all others are
  implicitly converted (if needed).
• DECODE considers two nulls to be equivalent.
• Select query can be used with in decode function.
• Decode can also use with where condition in select, update and Delete statements. and in insertion statement when try to insert values into a table
• DECODE function cannot call directly with in PL-SQL block

Syntax:

DECODE(expr1,expr2,result 1, result2);

If expr1 equals to expr2 then decode functions returns  Result 1  otherwise Result 2 as output.

Example queries using Decode statement.

SELECT DECODE(10,10,20,30) result FROM DUAL;

Output

result
20

SELECT DECODE(10,50,20,30) result FROM DUAL;

result
30

DECODE(expression,search1, result1, search2, result2,…., searchN, resultN, default)

The above syntax shows you,

• Decode functions compare the expression with other expressions like search1, search2, … searchN till the expression matches with other expression and returns the result that matches with expression.
• If comparison value of  expression not matches with any other expression then it results default value as output.

SELECT DECODE(10,2,30,40,50,60) result FROM DUAL;

result
60

• In the above query 10 is the expression that is trying  search  for matching expression in the decode function.
• 2, 40 are the searchable expressions and 60 is the default value that returns if expression is not matches any other searchable expression in the Decode function.
• In the above query  expression 10 is not matching with any other expression in the decode function and returning default value 60 as output.

SELECT DECODE(NULL,NULL,10,20) RESULT FROM DUAL;

result
10

Comparing a NULL with another NULL will return true using Decode statement. It will not be true if we compare NULL with other NULL value using any other statements or functions because NULL cannot be compared with another NULL value.

SELECT DECODE(NULL,NULL,(SELECT 10 FROM DUAL),(SELECT 20 FROM DUAL)) RESULT FROM DUAL;

Output

result
10

SELECT DECODE((SELECT NULL FROM DUAL),(SELECT NULL FROM DUAL),(SELECT 10 FROM DUAL),(SELECT 20 FROM DUAL)) RESULT FROM DUAL;

result
10

SELECT * from STD where fee=decode('x',1500,1500,28500);

Output

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: 
*Action:

When we try to compare expression of one data type with another data type using decode function, it will try to convert the first expression into searchable expression type format. if  expression is allowed to convert then decode will returns the result otherwise throws the exception stating as above output.

Example queries to use DECODE function in DML statements with where condition

select * from std where fee=decode(fee,1500,1500,28500);

UPDATE std SET FEE=DECODE(fee,1500,1000,28500) WHERE fee=decode(fee,1500,1500,28500);

DELETE FROM STD WHERE fee=decode(fee,1500,1500,28500);

INSERT INTO STD (STDNO,
SNAME,
JOINDATE,
FEE,
DEPTNO)
VALUES
(1,
'JAYCHANDRA',
SYSDATE,
decode(1500,1500,1500,28500),
4
);

INSERT INTO STD (STDNO,
SNAME,
JOINDATE,
FEE,
DEPTNO)
VALUES
(1,
'JAYCHANDRA',
SYSDATE,
decode(FEE,1500,1500,28500),
4
);

If we observe the above  insertion query with decode function, we are trying to use FEE column name in  decode function which is illegal.

Output

SQL Error: ORA-00984: column not allowed here
00984. 00000 - "column not allowed here"

Calling DECODE statement using pl-sql block

DECLARE
BEGIN
DECODE(10,10,20,30);
END;

If we try to call decode statement directly with in the pl-sql block, it throws exception stating that PLS-00221: ‘DECODE’ is not a procedure or is undefined.

To call decode statement using Plsql, we have to assign decode function to a variable to store the result.

DECLARE
a number;
BEGIN
SELECT DECODE(10,10,20,30) INTO A FROM DUAL;
dbms_output.put_line('The return value of Decode function ='||a);
END;

Output
The return value of Decode function =20

Note:

• function or pseudo-column ‘DECODE’ can be used inside a SQL statement only