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).
RE: range checking ??? URGENT
Title: RE: range checking ??? URGENT > -Original Message- > From: Leslie Lu [mailto:[EMAIL PROTECTED]] > > 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? Use decode in conjunction with sign function. sign (x - y) is 1 if x > y, 0 if x = y, -1 if x < y example: LQS> select 2 name, 3 to_char (gpa, '9.9') as gpa, 4 decode (sign (gpa - 4.0), 0, 'A', 1, 'A+', 5 decode (sign (gpa - 3.0), 0, 'B', 1, 'B', 6 decode (sign (gpa - 2.0), 0, 'C', 1, 'C', 7 decode (sign (gpa - 1.0), 0, 'D', 1, 'D', 'F') 8 ) 9 ) 10 ) as letter_grade 11 from student ; NAME GPA LE -- -- Einstein 5.0 A+ Smith 4.0 A Jones 3.5 B Mr. Chance 1.3 D
RE: range checking ??? URGENT
Which version? Does it have the case clause? -Original Message- Sent: Wednesday, November 14, 2001 4:50 PM To: Multiple recipients of list ORACLE-L 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: 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).
RE: range checking ??? URGENT
Title: RE: range checking ??? URGENT > -Original Message- > From: Leslie Lu [mailto:[EMAIL PROTECTED]] > > 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? P.S. I forgot to mention the case statement if you have Oracle 8.1 example: LQS> select n, 2 case when n < 100 then '< 100' 3 when n >= 100 and n < 200 then '100 <= n < 200' 4 when n >= 200 and n < 800 then '200 <= n < 800' 5 when n >= 800 and n < 1000 then '800 <= n < 1000' 6 end as n_description 7 from t 8 where mod (n, 50) = 0 ; N N_DESCRIPTION -- --- 50 < 100 100 100 <= n < 200 150 100 <= n < 200 200 200 <= n < 800 250 200 <= n < 800 300 200 <= n < 800 350 200 <= n < 800 400 200 <= n < 800 450 200 <= n < 800 500 200 <= n < 800 550 200 <= n < 800 600 200 <= n < 800 650 200 <= n < 800 700 200 <= n < 800 750 200 <= n < 800 800 800 <= n < 1000 850 800 <= n < 1000 900 800 <= n < 1000 950 800 <= n < 1000 19 ligne(s) sélectionnée(s).
RE: range checking ??? URGENT
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> / FOOSAL EMPNOMGR DEPTNO NVL(COMM,987) -- -- -- -- -- - 7369800 7369 7902 20 987 7900950 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 783910 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 s
Re: range checking ??? URGENT
If you are on 8i (I know it works on 8.1.6 and 8.1.7), you can use select case: SQL> select * from junk; X -- 10 15 20 25 SQL> select x, (case when x < 11 then 'small' when x < 21 then 'med' when x < 31 then 'large' end) from junk 2 / X (CASE -- - 10 small 15 med 20 med 25 large SQL> HTH Rob Pegram Oracle Certified DBA --- Leslie Lu <[EMAIL PROTECTED]> wrote: > 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). __ 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: Robert Pegram 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).
