Leslie,
If you are on 8.1.6 or higher, look into using the CASE statement -- it will
be a little easier to code and understand. If not, and it sounds like you
want one of many columns to print in a *single* column based on the range.
You can do this with the LEAST operator in conjunction with a DECODE (or the
SIGN operator instead of LEAST). Here is an example.
If Sal <=1000, print the EMPNO
If Sal Between 1001 and 2000 print the MGR
If Sal Between 2001 and 3000 print the deptno
Otherwise, Sal is greater than 3000, print the COMM.
By using the LEAST function in ascending order, I don't have to check both
boundaries. For example, if a salary is less than 1000, it will be true for
the first condition, empno will be returned, and sal will *not* be evaluated
for the remaining conditions (for which it would be TRUE if it were!).
1 select decode(least(sal,1000),sal,empno,
2 decode(least(sal,2000),sal,mgr,
3 decode(least(sal,3000),sal,deptno,nvl(comm,987)))) foo,
4 sal, empno,mgr,deptno,nvl(comm,987)
5 From emp
6* order by sal
SQL> /
FOO SAL EMPNO MGR DEPTNO NVL(COMM,987)
---------- ---------- ---------- ---------- ---------- -------------
7369 800 7369 7902 20 987
7900 950 7900 7698 30 987
7788 1100 7876 7788 20 987
7698 1250 7521 7698 30 500
7698 1250 7654 7698 30 1400
7782 1300 7934 7782 10 987
7698 1500 7844 7698 30 0
7698 1600 7499 7698 30 300
10 2450 7782 7839 10 987
30 2850 7698 7839 30 987
20 2975 7566 7839 20 987
20 3000 7788 7566 20 987
20 3000 7902 7566 20 987
987 5000 7839 10 987
Just in case I misunderstood and you wanted to "bucket" and accumulate items
in different columns, you can do something like this:
1 select (decode(greatest(sal,500),
sal,decode(least(sal,1000),sal,ENAME))) "500 - 1000",
2
(decode(greatest(sal,1001),sal,decode(least(sal,1500),sal,ENAME))) "1001 -
1500",
3
(decode(greatest(sal,1501),sal,decode(least(sal,2000),sal,ENAME))) "1501 -
2000",
4
(decode(greatest(sal,2001),sal,decode(least(sal,2500),sal,ENAME))) "2001 -
2500",
5
(decode(greatest(sal,2501),sal,decode(least(sal,5000),sal,ENAME))) "2501 -
5000"
6* from emp
7 /
500 - 1000 1001 - 150 1501 - 200 2001 - 250 2501 - 500
---------- ---------- ---------- ---------- ----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
snip
Now I need to check both high and low values. And once again, you could use
the SIGN function instead of LEAST. But, if the CASE statement is available,
you might find the code more readable for those not familiar with decode.
Even those that are sometimes go blind looking at super deep nested decodes
;-)
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Leslie Lu
> Sent: Wednesday, November 14, 2001 3:50 PM
> To: Multiple recipients of list ORACLE-L
> Subject: range checking ??? URGENT
>
>
> Hi all,
>
> Can decode work on a range, like if Code is > 100 and
> < 200, then name is A; if code>200 and code<300, then
> name is B; .... I have about 20 ranges to check. If
> decode cannot handle that, what's an easy way to do
> that?
>
> Thank you!
>
> Leslie
>
> __________________________________________________
> Do You Yahoo!?
> Find the one for you at Yahoo! Personals
> http://personals.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Leslie Lu
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
