Home » Other » Training & Certification » simple in,between clause doubt
simple in,between clause doubt [message #267264] Wed, 12 September 2007 21:20 Go to next message
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 Go to previous message
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
Previous Topic: Program to maintain a history of promise dates in dff 2 segments
Next Topic: help
Goto Forum:
  


Current Time: Fri Mar 29 03:17:04 CDT 2024