Consider below table – direction. It stores direction as abbreviation.
ID DIRECTION
———- ——————–
1 N
2 S
3 E
4 W
Write a query to display E for EAST, W for WEST etc.
select id,decode(direction,’N’,’NORTH’,’S’,’SOUTH’,’E’,’EAST’,’W’,’WEST’,null) as direction from direction;
ID DIRECTION
———- —————-
1 NORTH
2 SOUTH
3 EAST
4 WEST
In procedural way above logic can be written as
If DIRECTION = ‘N’ then
Value := ‘NORTH’;
elsif DIRECTION = ‘S’ then
Value := ‘SOUTH’;
elsif DIRECTION = ‘W’ then
Value := ‘WEST’;
elsif DIRECTION = ‘E’ then
Value := ‘EAST’;
else
value := null:
end if;
Nested decode function also allowed.
select id,decode(direction,’N’,’NORTH’,’S’,decode(‘SOUTH’,’SOUTH’,’SOUTH-WEST’,null),’E’,’EAST’,’W’,’WEST’,null) as direction from direction;
ID DIRECTION
———- —————-
1 NORTH
2 SOUTH-WEST
3 EAST
4 WEST
If it is S it converts S to SOUTH and SOUTH to SOUTH-WEST.
Decode function is one of the most widely used function in Oracle SQL.
Note : – All examples tested in Oracle 11g Release 2.
How to convert rows into columns using dcode
Using pivot