I got a SQL query as follows:
SELECT <blah>, <blah>, ....
FROM ap_invoices_all a
,ap_invoice_distributions_all b
,ap_suppliers i
,ap_supplier_sites_all assa
,ap_payment_schedules_all k
,ap_terms_tl m
,ap_invoice_lines_all aila
,per_all_people_f papf
WHERE a.invoice_id = b.invoice_id
AND a.invoice_id = k.invoice_id(+)
AND i.vendor_id = a.vendor_id
and a.vendor_site_id = assa.vendor_site_id
AND m.term_id = a.terms_id
AND b.invoice_id = aila.invoice_id
AND b.invoice_line_number = aila.line_number
AND a.requester_id = papf.person_id (+)
AND trunc(a.last_update_date) between papf.effective_start_date (+) and papf.effective_end_date (+)
AND k.payment_num = 1
AND (a.cancelled_date is NULL)
AND a.payment_status_flag BETWEEN 'N' AND 'P'
order by a.invoice_id, b.invoice_distribution_id;
Here the joins are in the form of comma separated tables and Oracle (+) operator which means LEFT OUTER JOIN. I have to convert those comma separated tables in FROM clause to proper JOIN statements.
I have tried this:
SELECT <blah>, <blah>, ...
FROM ap_invoices_all a
JOIN ap_invoice_distributions_all b ON a.invoice_id = b.invoice_id
JOIN ap_supplier_sites_all assa ON a.vendor_site_id = assa.vendor_site_id
LEFT OUTER JOIN ap_payment_schedules_all k ON a.invoice_id = k.invoice_id
LEFT OUTER JOIN per_all_people_f papf ON a.requester_id = papf.person_id
JOIN ap_terms_tl m ON m.term_id = a.terms_id
JOIN ap_suppliers i ON i.vendor_id = a.vendor_id
JOIN ap_invoice_lines_all aila ON b.invoice_id = aila.invoice_id
AND b.invoice_line_number = aila.line_number
WHERE k.payment_num = 1
AND ISNULL(a.Cancelled_Date)
AND a.payment_status_flag BETWEEN 'N' AND 'P'
AND DATE_TRUNC("DAY", a.last_update_date) between papf.effective_start_date and papf.effective_end_date
order by a.invoice_id, b.invoice_distribution_id;
But my converted query provides different result from the original query. Could anyone please help me understand where I am wrong and what should be the proper join statements?
Thanks, Soubhik