[SPCONV-ERR(<NN>)]:[Parsing(<NAME>)]('LEFT JOIN') Complex OUTER JOINS are not Reliably Converted

The T-SQL to PL/SQL parser in the Migration Workbench is unable to convert all cases of OUTER JOINS. For example, when the parser encounters a statement such as the following:

select a LEFT OUTER JOIN b ON d = e

it does not know which table d and e are in. Therefore, it cannot add the Oracle style (+) OUTER JOIN symbols.

Statements such as the following:

select a LEFT OUTER JOIN b ON a.d = b.e

are correctly converted as follows:

select a, b whereba.d = b.e(+);

The following example illustrates how the parsercould produce this error:

CREATE PROC myouterjoin
AS
BEGIN
declare @mycol1 varchar(30)
declare @mycol2 varchar(30)
select @mycol1=tab1.col1, @mycol2=tab2.col2 from tab1
LEFT OUTER JOIN tab2 on tab1.id=tab2.id
END

The following PL/SQL code is produced by the parser:

CREATE OR REPLACE PROCEDURE myouterjoin
AS
StoO_selcnt INTEGER;
StoO_error  INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
mycol1  VARCHAR2(30);
mycol2  VARCHAR2(30);
BEGIN
 /*[SPCONV-ERR(7)]:[Parsing(myouterjoin)]('LEFT JOIN') Complex outer joins are not reliably converted*/
 BEGIN
  StoO_rowcnt := 0;
  StoO_selcnt := 0;
  StoO_error  := 0;

  SELECT   tab1.col1,  tab2.col2
  INTO myouterjoin.mycol1, myouterjoin.mycol2 FROM tab1, tab2
  WHERE (tab1.id = tab2.id(+))
  ;
  StoO_rowcnt := SQL%ROWCOUNT;

  EXCEPTION
   WHEN TOO_MANY_ROWS THEN
    StoO_rowcnt := 2;
   WHEN OTHERS THEN
    StoO_rowcnt := 0;
    StoO_selcnt := 0;
    StoO_error := SQLCODE;
    StoO_errmsg := SQLERRM;
 END;
END;
 

This simple OUTER JOIN does not require alteration.