| Nested Select and Min [message #371380] |
Wed, 11 October 2000 07:42 |
jr
Messages: 9
Registered: October 2000
|
Junior Member |
|
|
I am trying to get the min date with the id and calculated total with the following query. The query currently returns these selctions but I get multiple IDs and dates sometimes.
Table a
id wdate qty
Table b
id wdate orderid
Table c
orderid qty amt
select a.id, a.qty, a.wdate,
(a.qty - sum(c.qty * c.amt)) As Total
from Table a, Order b,
Detail c
where trunc(b.wdate) = trunc(a.wdate)
and b.id = a.id
and b.orderid = c.orderid
and a.id >= 'B1'
and a.qty - (select nvl(sum(c.qty*c.amt),0)
from Order b, Detail c
where trunc(b.wdate) = trunc(a.wdate)
and b.id = a.id
and b.orderid = c.orderid) >= 4
and trunc(a.wdate) = (select min(a.wdate)
from Table d
where d.id = a.id
and trunc(a.wdate) >= '09-OCT-2000')
group by a.id, a.qty, a.wdate
order by a.id;
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
