Subject : Interesting DECODE question
----- Message Text -----
On Tue, 9 Mar 1999, dharmesh bengali wrote:
>  Hi Ari,
> 
>  while looking for an answer to my question I came across your web
> site. You have answers for every questions. However, I did not find an
> answer to  my question, maybe you can help.
>
>  1. select empno,ename,job,sal,decode(sal,1500,'very less salary',
>       'good salary') status from emp;  
>
>     Using decode i want to display 'very less salary' for employees
> who earn salary less then 1500 and 'good salary' for employees
>     who earn salary more then 1500.
>
>     i tried using
>
>       select empno,ename,job,sal,decode(sal,sal < 1500,'very less
> salary','good salary')status from emp;   
>  
>     but it didn't work.
>  
Dharmesh,

Your question can be solved using DECODE and SIGN.
SIGN(1500-SALARY) is 1 if SALARY > 1500.
SIGN(1500-SALARY) is 0 if SALARY = 1500.
SIGN(1500-SALARY) is -1 if SALARY < 1500.

So, putting it all together, you caan issue:

SELECT empno, ename, job, sal,
DECODE(SIGN(1500-sal),-1,'very less salary',
                         'good salary')
FROM emp
/
   
Best regards,
   
-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 275+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page ex.htm"> Back to Ari Kaplan's Home Page