OraFAQ Forum: Training & Certification » simple in,between clause doubt

Home » Other » Training & Certification » simple in,between clause doubt

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

simple in,between clause doubt [message #267264]

Wed, 12 September 2007 21:20

ashish2345
Messages: 50
Registered: September 2007

Member

Hi friends,

i have two tables emp and salgrade.

select distinct sal from emp;

SAL
------
950
1200
1250
1300
1500
1600
2450
2850
2975
3000
5000
5654

and

select * from salgrade;

GRADE LOSAL HISAL
----- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

now i want to get salaries which lie in the range of hisal,losal of salgrade.. iused following query

1 select ename,sal from emp
2 where sal >= all(select losal from salgrade)
3* and sal < all(select hisal from salgrade)
SQL> /

no rows selected

please tell why its not working and how to use between clause in this case

rather only inline view is working fine

select ename,mm,sal,maxx from emp,(select min(losal)mm from salgrade ),(select max(hisal)maxx from salgrade) where sal >= mm and sal <maxx

[Updated on: Wed, 12 September 2007 21:28]

Report message to a moderator

Re: simple in,between clause doubt [message #267265 is a reply to message #267264]

Wed, 12 September 2007 21:50

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

sal >= all(select losal from salgrade)

This means you want salaries greater or equal to every LOSAL in the SALGRADE table. LOSALS range from 700 to 3001, so the only salaries that are greater or equal to every LOSAL would be those >= 3001.

sal < all(select hisal from salgrade)

This means you want salaries less than every HIGHSAL in the SALGRADE table. HIGHSALS range from 1200 to 9999, so the only salaries that are less than every HIGHSAL would be those < 1200.

Putting these together, you want:

WHERE SAL >= 3001
AND   SAL < 1200

Can you see now why this returns no rows?

You can get what you are after by replacing the ALL() subqueries with ANY(), however you should note that these operators are not in common usage. The usual way to do this in Oracle is MIN and MAX: SAL > (SELECT MIN(losal) FROM salgrade)

Ross Leishman

Report message to a moderator

Previous Topic:	Program to maintain a history of promise dates in dff 2 segments
Next Topic:	help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 19:55:38 CST 2024