Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem with full outer join together with group functions

216630Aug 22 2006 — edited Sep 6 2006
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 4 2006
Added on Aug 22 2006
21 comments
2,109 views