[SPCONV-ERR(<NN>)]:ORDER BY Statement Ignored

ORDER BY statements in subqueries are not allowed in Oracle. Therefore, a flag has been added to comment them out in some cases. If the ORDER BY clause causes a problem it can be removed.

The following example illustrates how the T-SQL to PL/SQL parser in the Migration Workbench could produce this error:

CREATE PROC proc1
AS
declare @mycol1 numeric
declare @mycol2 numeric
select  @mycol2=table2.col2
FROM table2 tab21
WHERE EXISTS (select col2 from table2 t2 where col2 max(table1.col1)and
tab21.id=t2.id order by col2)

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

CREATE OR REPLACE PROCEDURE proc1
AS
StoO_selcnt INTEGER;
StoO_error  INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
StoO_sqlstatus INTEGER;
mycol1  NUMERIC;
mycol2  NUMERIC;
BEGIN
 BEGIN
  StoO_rowcnt := 0;
  StoO_selcnt := 0;
  StoO_error  := 0;

  SELECT   table2.col2
  INTO proc1.mycol2 FROM table2 tab21
  WHERE EXISTS (
  SELECT  col2
   FROM table2 t2
  WHERE col2MAX(table1.col1)
   and tab21.id = t2.id

  /*[SPCONV-ERR(8)]:ORDER BY statement ignored*/
  );
  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 proc1;

No change is required in this case; the ORDER BY clause was redundant.