0

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

Soubhik
  • 11
  • 3
  • `trunc(a.last_update_date) between papf.effective_start_date (+) and papf.effective_end_date (+)` is a left join between `ap_invoices_all` and `per_all_people_f`, so should be placed in the `join` condition – astentx Aug 02 '22 at 12:04
  • Does this answer your question? [SQL convert from Oracle to ANSI JOIN](https://stackoverflow.com/questions/69282871/sql-convert-from-oracle-to-ansi-join) – astentx Aug 02 '22 at 18:45
  • No, my Oracle query is slightly different. – Soubhik Aug 11 '22 at 16:59
  • What do you mean by "slightly different"? SQL Developer has a feature to convert Oracle joins to ANSI joins. It is not about your exact query. How to fix the join in your rewritten query: see my comment above – astentx Aug 11 '22 at 21:38
  • @astentx in the post you referenced, for tables a, b, c there is a relation between a & c, a & b and b & c. But in my case b & c do not have any relation between them. I doubt left outer join shall conclude to inner join as per the stack post you referenced. In SQL Developer Version 4.1.5.21 Build MAIN-21.78 , I am not getting the "Convert to ANSI join" feature under R-click -> "Refactoring" option – Soubhik Aug 14 '22 at 16:12

0 Answers0