Changing the old SQL lanuage to the new

Question

I am currently a Trainee trying to develop my SQL. Could someone help me with changing the old language to the new(orale 11g or sql 2014) Below is an example that I am current changing;

select apa.invoicE_num ap_invoice_num
, apa.creation_date
,  rat.TRX_NUMBER ar_trx_number
, rat.creation_date loaded_into_ar
,asw.inserted_date
, decode(pvs.org_id,86,'SP',87,'SV')
, pv.vendor_nam
,pv.segment1,
pvs.vendor_site_code
, asw.attribute6
, asw.invoice_amount
, asw.INVOICE_NUM

from asw.asw_ap_invoices_interface_aud asw
, ra_customer_trx_all rat
, po_vendor_sites_all pvs
, po_vendors pv
, ap_invoices_all apa

where asw.invoice_num = rat.trx_number (+)
and asw.VENDOR_SITE_ID = pvs.vendor_site_id
and pvs.vendor_id = pv.vendor_id
and asw.invoice_num = apa.invoicE_num (+)
and asw.invoice_amount = apa.invoicE_amount (+)
and asw.vendor_site_id = apa.vendor_site_id (+)
and asw.attribute_category = 'Retek Import'
and asw.attribute6 in ('MB','DA','RM','SB')
and trunc(inserted_date) > '29-OCT-2015'

Thank you for reading this and I hope you can help!

See [**Outer-joins Oracle vs ANSI syntax**](http://lalitkumarb.com/2015/08/12/outer-joins-oracle-vs-ansi-syntax/) — Lalit Kumar B, Nov 09 '15 at 11:39
When you see (+) in the old-style join conditions, that indicates an outer join. In order to convert this query to an ANSI style join, first off, I'd look at the where clauses and work out which ones were join conditions, then I'd convert the commas in the from clause into the relevant join (eg left/right outer join, inner join etc) and then move the predicate into the on clause. Does that help? Give it a try and update your question with what you've managed to achieve, and then we can help you better. — Boneist, Nov 09 '15 at 11:41
`trunc(inserted_date) > '29-OCT-2015'` Why are you comparing a **DATE** with a **STRING**. You should not depend on local NLS settings, always use **TO_DATE** to explicitly convert the string into DATE with proper **format model**. Else, if you are not concerned about the time element, you could use the **ANSI Date literal** which is **NLS independent as it uses a **fixed format** `DATE 'YYYY-MM-DD'` — Lalit Kumar B, Nov 09 '15 at 11:41
Also, please, please, PLEASE convert strings-that-represent-dates to a date explicitly. You can either use `to_date()` with the appropriate format mask (e.g. `to_date('29/10/2015', 'dd/mm/yyyy')`) or the ANSI date literal (e.g. `DATE '2015-10-29'`). Failing to do so means you're relying on the NLS_DATE_FORMAT parameter always being the same from computer to computer. However, as this is something that can be changed, it's not safe to rely on that, so now you have introduced a bug. Bad idea, especially in production code! — Boneist, Nov 09 '15 at 11:44

score 2 · Answer 1 · answered Nov 09 '15 at 11:40

SELECT *
FROM a, b
WHERE a.id=b.id(+)

the same as

SELECT *
FROM a LEFT OUTER JOIN b ON a.id=b.id

So in your case:

select apa.invoicE_num ap_invoice_num
, apa.creation_date
,  rat.TRX_NUMBER ar_trx_number
, rat.creation_date loaded_into_ar
,asw.inserted_date
, decode(pvs.org_id,86,'SP',87,'SV')
, pv.vendor_nam
,pv.segment1,
pvs.vendor_site_code
, asw.attribute6
, asw.invoice_amount
, asw.INVOICE_NUM

from asw.asw_ap_invoices_interface_aud asw
left outer join ra_customer_trx_all rat on asw.invoice_num = rat.trx_number
inner join po_vendor_sites_all on pvs asw.VENDOR_SITE_ID = pvs.vendor_site_id
inner join po_vendors pv on pvs.vendor_id = pv.vendor_id
left outer join ap_invoices_all apa on asw.invoice_amount = apa.invoicE_amount and asw.vendor_site_id = apa.vendor_site_id

where asw.attribute_category = 'Retek Import'
and asw.attribute6 in ('MB','DA','RM','SB')
and trunc(inserted_date) > '29-OCT-2015'

Changing the old SQL lanuage to the new

1 Answers1