Re: Outer join

That is because foo_bar has twice the number of rows for C_13='U'. For every unique combination of C_01 to C_15 and FY in foo_bar foo_tmp contains 4 rows, one each for PD 1, 2, 3, and 4. Foo_bar contains 9 rows, foo_tmp has 36.

My description "the content of foo_tmp is designed to have pd values of 1 to 4 for every combination of col_01 to col_15 and fy" was incomplete and misleading. It should be

"the content of foo_tmp is designed to have pd values of 1 to 4 for every unique combination of col_01 to col_15 and fy in foo_bar"

At 12:17 AM 11/10/2008, Toon Koppelaars wrote:
>Wolfgang,
>
>I took a closer look at the contents of your tables.
>
>The second 12 rows in footmp are the same as the first 12 rows...
>Your query is performing correctly. Your statement: "the content of
>foo_tmp is designed to have pd values of 1 to 4 for every
>combination of col_01 to col_15 and fy" is not true.
>It (footmp) has twice the number of such rows for C13='U'
>
>Toon
>
>
>with the following contents
>
>foo_bar:
>c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c1 c13 c14
>c15 pd fy amt1 amt2 amt3
>----- ------ ------ ---- ----- --- -- --- -- --- -------- -- --- ---
>--- ----- --- ----------- ----------- -----------
>DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR
>EUR 2008 3 -1,384.62 -1,384.62 -1,384.62
>DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR
>USD 2008 3 -1,384.62 -2,041.55 -1,384.62
>DEF03 112233 556677 7777 ABC01 99 AEIOU B USD
>USD 2008 3 -2,041.55 0.00 0.00
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>EUR 2008 1 -5,204.55 -5,204.55 -5,204.55
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>EUR 2008 2 -10,489.72 -10,489.72 -10,489.72
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>USD 2008 1 -5,204.55 -7,663.18 -5,204.55
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>USD 2008 2 -10,489.72 -15,445.06 -10,489.72
>DEF03 112233 556677 7777 ABC01 99 AEIOU U USD
>USD 2008 1 -7,663.18 0.00 0.00
>DEF03 112233 556677 7777 ABC01 99 AEIOU U USD
>USD 2008 2 -15,445.06 0.00 0.00
>
>foo_tmp:
>c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c1 c13 c14
>c15 pd fy
>----- ------ ------ ---- ----- --- -- --- -- --- -------- -- --- ---
>--- ----- ---
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>EUR 2008 1
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>EUR 2008 2
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>EUR 2008 3
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>EUR 2008 4
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>USD 2008 1
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>USD 2008 2
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>USD 2008 3
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>USD 2008 4
>DEF03 112233 556677 7777 ABC01 99 AEIOU U USD
>USD 2008 1
>DEF03 112233 556677 7777 ABC01 99 AEIOU U USD
>USD 2008 2
>DEF03 112233 556677 7777 ABC01 99 AEIOU U USD
>USD 2008 3
>DEF03 112233 556677 7777 ABC01 99 AEIOU U USD
>USD 2008 4
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>EUR 2008 1
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>EUR 2008 2
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>EUR 2008 3
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>EUR 2008 4
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>USD 2008 1
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>USD 2008 2
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>USD 2008 3
>DEF03 112233 556677 7777 ABC01 99 AEIOU U EUR
>USD 2008 4
>DEF03 112233 556677 7777 ABC01 99 AEIOU U USD
>USD 2008 1
>DEF03 112233 556677 7777 ABC01 99 AEIOU U USD
>USD 2008 2
>DEF03 112233 556677 7777 ABC01 99 AEIOU U USD
>USD 2008 3
>DEF03 112233 556677 7777 ABC01 99 AEIOU U USD
>USD 2008 4
>DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR
>EUR 2008 1
>DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR
>EUR 2008 2
>DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR
>EUR 2008 3
>DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR
>EUR 2008 4
>DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR
>USD 2008 1
>DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR
>USD 2008 2
>DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR
>USD 2008 3
>DEF03 112233 556677 7777 ABC01 99 AEIOU B EUR
>USD 2008 4
>DEF03 112233 556677 7777 ABC01 99 AEIOU B USD
>USD 2008 1
>DEF03 112233 556677 7777 ABC01 99 AEIOU B USD
>USD 2008 2
>DEF03 112233 556677 7777 ABC01 99 AEIOU B USD
>USD 2008 3
>DEF03 112233 556677 7777 ABC01 99 AEIOU B USD
>USD 2008 4
>
>the content of foo_tmp is designed to have pd values of 1 to 4 for
>every combination of col_01 to col_15 and fy

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l