Using Aggregate's in multiple Subqueries
446867Jul 5 2005 — edited Jul 6 2005I have a table I want to join to itself to get the min and max values for the date column and return multiple columns. But then join that to another table on different columns.
Example
SELECT
A.F1,
B.F1,
(select C.weight
from C
where
(C.F2 like '%abc%') and
(C.v = A.v) and (C.F3 <> 'Y') and
(C.date = (Select min(date)
FROM C2
Where (C2.F2 like '%abc%') and
(C2.v = A.v) and
(C2.F3 <> 'Y')
)
)
) as Weight,
(select C3.seq
from C3
where
(C3.F2 like '%abc%') and
(C3.v = A.v) and
(C3.F3 <> 'Y') and
(C3.date = (Select min(date)
FROM C4
Where (C4.F2 like '%abc%') and
(C4.v = A.v) and
(C4.F3 <> 'Y')
)
)
) as Seq
FROM A
join B on a1.f1 = B1.f1
where status = 0
The above works, but is slow (1 min)
If I take the min() query and join it to table A directly and use the column names, it's much faster 32ms but only returns matching rows. I want unmatched rows as well.
If I try to outer join on the subquery it gives me an Oracle Error about cannot outer join to tables with subqueries.
This is a small example. I'm actually joining 4 times on same table for min(date) and 4 times for max(date).
Can someone give me a better way to do this?