Home » Other » Training & Certification » simple in,between clause doubt
simple in,between clause doubt Wed, 12 September 2007 21:20
 ashish2345 Messages: 50Registered: 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

----- ---------- ----------
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: 3727Registered: 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: Wed Jul 26 00:16:25 CDT 2017

Total time taken to generate the page: 0.08679 seconds