Hello,
I have two tables tab1 and tab2 with a common column col_a. To get informations from both tables I use an full outer join. I also need to aggrgate informations from each table therefore I want to use inline views with GROUP BY.
SELECT col_a
,alias1.col_x
,alias2.col_y
,alias1.amount1
,alias2.amount2
FROM (SELECT tab1.col_a
,tab1.col_x
,SUM(CASE WHEN condition1
THEN subtab1.amount
ELSE 0
END
) amount1
FROM tab1
,subtab1
WHERE tab1.col_x = subtab1.col_x
GROUP BY tab1.col_x
) alias1
FULL OUTER JOIN
(SELECT tab2.col_a
,tab2.col_y
,SUM(CASE WHEN condition2
THEN subtab2.amount
ELSE 0
END
) amount2
FROM tab2
,subtab2
WHERE tab2.col_y = subtab2.col_y
GROUP BY tab1.col_x
) alias2
USING (col_a)
The statement works without the SUM() and each inline view individually works. It also works, when I define the inline views as views in the database, but not when I put it all together.
Does anybody know something about a restriction of aggregate functions within inlineviews or/and joins?
Regards
Marcus