Outer Join 1

[zMonk]

I have this query and for some reason it does not return the same number of rows as before it was joined with table "migr.legacy". It is a mapping to the lagacy dept. And ceratain combinations are not mapped - specifically
"AND L_MAP.oracle_business_segment(+) = gcc.segment7" is a missing from the combination e.g it is present on "gcc" but missing on legacy...


I am stuck on this does anyone know any reason why ?



select AP.AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID, INVOICE_NUM, INVOICE_AMOUNT, INVOICE_DATE, GL_DATE, AMOUNT, segment1 , segment2 ,L_MAP.DEPT_NO DEPT
from AP.AP_INVOICES_ALL,
AP.AP_INVOICE_DISTRIBUTIONS_ALL,
gl.gl_code_combinations gcc,
MIGR.LEGACY L_MAP
where VENDOR_ID=87487 and
AP.AP_INVOICE_DISTRIBUTIONS_ALL.PERIOD_NAME IN ('JAN-03','FEB-03','MAR-03','APR-03','MAY-03') AND
AP.AP_INVOICES_ALL.INVOICE_ID=AP.AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID and
code_combination_id=DIST_CODE_COMBINATION_ID AND
L_MAP.oracle_entity = gcc.segment1
AND L_MAP.oracle_location = gcc.segment4
AND L_MAP.oracle_department = gcc.segment5
AND L_MAP.oracle_business_segment(+) = gcc.segment7
AND L_MAP.inactive_flag = 'N';

 

[Turkbear]

For an OUTER JOIN to work my understanding is that ALL where statements referencing fields in the 'outer joined' table
( in your instance, MIGR.LEGACY ) must have the (+) symbol appended, so it should be something like :

 L_MAP.oracle_entity(+) = gcc.segment1
      AND L_MAP.oracle_location(+) = gcc.segment4
      AND L_MAP.oracle_department(+) = gcc.segment5
      AND L_MAP.oracle_business_segment(+) = gcc.segment7
      AND L_MAP.inactive_flag(+) = 'N';

Give it a try...

 

[zMonk]

Bingo thank you very much! It has been killing me for the past two days! I will though need to rewrite the quiery, because I need to eliminate the "Y" inactive flag. I hope writing a query in the "FROM" section and puting a where clause on top of it will do ....