Convert oracle join to ansi join

Toad for Oracle
1

Hi, could someone please help me convert the following query in order to use ansi joins? Thanks!

SELECT *

FROM VRW_ECL_EXP_DCLF_COMM_DTL_M0 DECLARACION,
VRW_ECL_EXP_DCLF_PRDT_DTL_M0 DETALLE_ITEM,
VRM_ECG_TRSP_DOC_M0 DATOS_CARGA,
VRM_ECG_MNFT_EXP_DCL_M0 MAPEO_DCL_BL

where DECLARACION.Exp_dcl_mng_no = DETALLE_ITEM.Exp_dcl_mng_no
and MAPEO_DCL_BL.EXP_DCL_NO(+) = DECLARACION.EXP_DCL_MNG_NO
and MAPEO_DCL_BL.MRN = DATOS_CARGA.MRN(+)
and MAPEO_DCL_BL.MSN = DATOS_CARGA.MSN(+)
and MAPEO_DCL_BL.HSN = DATOS_CARGA.HSN(+)
and DECLARACION.dclf_send_dt between to_date(’&FECHA_DESDE’||‘00:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
and to_date(’&FECHA_HASTA’||‘23:59:59’,‘yyyy-mm-dd hh24:mi:ss’)

2

Hi Renzo,

The following applies to Toad 12, your version might be slightly different.

Connect to the schema which owns the tables, and copy that query from

your email, or wherever, into the editor. Test - how many rows etc do

you get. Make a note.

Select the query, without the trailing semi-colon, and edit->copy to get

it in the clipboard.

Now, Database->Report->Query Builder.

At the bottom, click on the Generated Query tab, and paste your query

into the editor that appears there.

You will need to make sure you don;t have a trailing semi-colon, or it

won’t work from here on in.

Press CTRL+SHIFT+D to synchronise the query and the diagram above it.

After a small pause, the diagram will show the tables involved in the

query.

Just above the Generate Query where you pasted your original query,

there’s a small toolbar. One of the buttons has the text “ANSI” and a

green arrow. Hover over it and you will see the tooltip "ANSI Join

Syntax" - click the button.

The generated query changes to ANSI syntax. If you click the same button

again, it changes back to old-style Oracle syntax.

Next to the ANSI button, to the left, there’s a button with a gree arrow

on it, and the tooltip “Execute Query”. Click it. You should see the

same results on the “Query Results” tab.

Check that the results match your original query’s results, and if so,

do some more testing before putting this into production.

The second button on the toolbar is a clipboard, use it to copy the

generated query to the clipboard ready to paste elsewhere.

HTH

–

Cheers,

Norm. [TeamT]

3

Or if you have 12.1 or newer version, just right click the query in Toad editor and select Refactor/Convert to ANSI Join Syntax.

But as Norm writes, you must test before and after that you get the same results. You can’t really rely on this feature yet.

4

And this is the result from Refactor/Convert to ANSI Join Syntax:

SELECT *

FROM VRW_ECL_EXP_DCLF_COMM_DTL_M0 DECLARACION

CROSS JOIN VRW_ECL_EXP_DCLF_PRDT_DTL_M0 DETALLE_ITEM

CROSS JOIN VRM_ECG_TRSP_DOC_M0 DATOS_CARGA

CROSS JOIN VRM_ECG_MNFT_EXP_DCL_M0 MAPEO_DCL_BL

WHERE ( ( ( ( (DECLARACION.Exp_dcl_mng_no =

DETALLE_ITEM.Exp_dcl_mng_no)

AND ( (MAPEO_DCL_BL.EXP_DCL_NO /(+)/

) =

DECLARACION.EXP_DCL_MNG_NO))

AND (MAPEO_DCL_BL.MRN = (DATOS_CARGA.MRN /(+)/

)))

AND (MAPEO_DCL_BL.MSN = (DATOS_CARGA.MSN /(+)/

)))

AND (MAPEO_DCL_BL.HSN = (DATOS_CARGA.HSN /(+)/

)))

AND (DECLARACION.dclf_send_dt BETWEEN TO_DATE (

‘&FECHA_DESDE’ || ‘00:00:00’,

‘yyyy-mm-dd hh24:mi:ss’)

AND TO_DATE (

‘&FECHA_HASTA’ || ‘23:59:59’,

‘yyyy-mm-dd hh24:mi:ss’))

With warnings for ‘&FECHA_DESDE’ || ‘00:00:00’ and ‘&FECHA_HASTA’ || ‘23:59:59’:

Rule 4603: Avoid leteral concatinations at runtime