RE: range checking ??? URGENT

From: Larry Elkins <elkinsl_at_flash.net>
Date: Wed, 14 Nov 2001 15:14:41 -0800
Message-ID: <F001.003C52CF.20011114145533@fatcity.com>

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

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
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]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: leslie_y_lu_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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: elkinsl_at_flash.net

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: ListGuru_at_fatcity.com (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).

Received on Wed Nov 14 2001 - 17:14:41 CST