Home » SQL & PL/SQL » SQL & PL/SQL » Query regarding Decode() function
Query regarding Decode() function [message #225088] Sat, 17 March 2007 04:50 Go to next message
Messages: 1
Registered: March 2007
Junior Member
Please can any body explain how the decode command works.

create or replace view emp_view as(select empno,ename,job,mgr,hiredate,
decode(ename,user,sal,'KING',sal,0)as sal,
decode(ename,user,comm,'KING',comm,0)as comm,
deptno from emp);

Thanks and Regards,
Re: Query regarding Decode() function [message #225091 is a reply to message #225088] Sat, 17 March 2007 05:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2108
Registered: October 2003
Location: Hyderabad, India
Senior Member
Did you try anything else except posting here?
At least any sql book?

Instead you could just type oracle decode in google....


[Updated on: Sat, 17 March 2007 05:50]

Report message to a moderator

Re: Query regarding Decode() function [message #225255 is a reply to message #225088] Mon, 19 March 2007 06:37 Go to previous message
Messages: 1
Registered: March 2007
Junior Member
Hi there

A basic DECODE takes the first value and compares it with the second value. If they are the same the third value will be returned, if they are different the fourth value will be returned (when I say the same I mean equal to).

This is a simple example of a decode:

select DECODE(commission, null, 'No', 'Yes')
from employees;

So to explain this example, if the commission is null it will return the string 'No', but if there is commission ie it is not null it will return the string 'Yes'. You will probably have an example of the employee table somewhere if you have been on a SQL course, try running this against it and see the results for yourself - I find this always helps me.

Now see the following example which is similar to the first decode in your example:

select decode(ename,
'HILL', 54000,
'KING', 47000,
0) as sal
from dual;

If ename is HILL it will return 54000, if ename is KING it will return 47000. If ename is not equal to HILL or KING it will return 0. The 0 in this case is known as the default value.

Hope this helps.
Previous Topic: sql help
Next Topic: Need help in optimizing a query
Goto Forum:

Current Time: Wed Aug 16 21:03:01 CDT 2017

Total time taken to generate the page: 0.12788 seconds