Re: Outer join

From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
To: "Toon Koppelaars" <toon@xxxxxxxxxxx>
Date: Mon, 10 Nov 2008 06:12:38 -0700

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_barfoo_tmp contains 4 rows, one each for PD 1, 2, 3, and 4. Foo_barcontains 9 rows, foo_tmp has 36.

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

"the content of foo_tmp is designed to have pd values of 1 to 4 forevery 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 offoo_tmp is designed to have pd values of 1 to 4 for everycombination 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 c14c15 pd fy amt1 amt2 amt3----- ------ ------ ---- ----- --- -- --- -- --- -------- -- --- ------ ----- --- ----------- ----------- -----------DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 3 -1,384.62 -1,384.62 -1,384.62DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 3 -1,384.62 -2,041.55 -1,384.62DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 3 -2,041.55 0.00 0.00DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 1 -5,204.55 -5,204.55 -5,204.55DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 2 -10,489.72 -10,489.72 -10,489.72DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 1 -5,204.55 -7,663.18 -5,204.55DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 2 -10,489.72 -15,445.06 -10,489.72DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 1 -7,663.18 0.00 0.00DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 2 -15,445.06 0.00 0.00
foo_tmp:
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c1 c13 c14c15 pd fy----- ------ ------ ---- ----- --- -- --- -- --- -------- -- --- ------ ----- ---DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EUREUR 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U EURUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU U USDUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU B EUREUR 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU B EURUSD 2008 4DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 1DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 2DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 3DEF03 112233 556677 7777 ABC01 99 AEIOU B USDUSD 2008 4
the content of foo_tmp is designed to have pd values of 1 to 4 forevery combination of col_01 to col_15 and fy


Regards

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

References:
- purge_audit_template
  - From: Ray Stell
- Outer join
  - From: Wolfgang Breitling

Re: Outer join

Other related posts: