Oracle-Conveting SQL to ANSI SQL

Question

This is regarding converting query to ANSI SQL. I tried writing this query with Oracle old syntax and it threw the following error so I ended up changing it as shown below . After researching, I found that ANSI SQL supports such requirements.

ERROR :a table may be outer joined to at most one other table tips

Here is the query that I wrote which is working but it would be great to know if there are ways this can be re-written in ANSI-SQL or by using old outer join syntax. I am looking for the union queries to be re-written as others in the with clause work fine.

    WITH BOM_PARENT AS (
    SELECT MX.SAP_MATNR MATNR
         , TRIM(I.IPRODUCTION) IPRODUCTION
         , TRIM(I.IDRAWING) IDRAWING
         , TRIM(M.BCHLD) BCHLD
         , M.BSEQ BSEQ
         , PX.WERKS WERKS
         , M.BPSCP MENGE
         , UX.SAP_UOM MEINS
         , I.IUMS
     FROM XX_MAIN.XX_BPCS_IIM I
        , XX_MAIN.XX_BPCS_MPB M
        , XX_MAIN.XX_MATER_XREF MX
        , XX_MAIN.XX_TRUNK_XREF PX
        , XX_MAIN.XX_MP_UNIT_XREF UX
    WHERE TRIM(I.IPRODUCTION) = TRIM(M.BPROD) 
      AND TRIM(MX.PROD_MATNR) = TRIM(I.IPRODUCTION)
      AND MX.CONV_FACTOR = TO_CHAR(I.IUMCN) 
      AND I.IUMS  = UX.LEGACY_UOM
      AND UX.SOURCE = 'AP'
      AND I.SOURCE = PX.SOURCE
      AND I.ENTITY = PX.LEGACY_PLANT
      AND I.ENTITY = 'SG' 
      AND I.IITYP = '4'
      --AND PX.WERKS IN ('1379')
      AND MX.SAP_MTART <> 'ZPRD'
    )
    , BOM_CHILDS AS
    (
     SELECT  B.*,
             X.SAP_MATNR IDNRK
           , ROW_NUMBER () OVER ( PARTITION BY B.MATNR,B.WERKS ORDER BY B.MATNR,B.WERKS) ID_ITEM_NO  
           , X.PROD_MATNR IDNRK_IPRODUCTION
           , X.DRAWING_MATNR IDNRK_IDRAWING     
      FROM  BOM_PARENT B
          , XX_MAIN.XX_MATER_XREF X
    WHERE  B.BCHLD = TRIM(X.PROD_MATNR )
      AND X.SAP_MTART <> 'ZPRD'
      AND X.SOURCE = 'AP'
    )
    SELECT DISTINCT C.MATNR
         , C.IPRODUCTION
         , C.IDRAWING
         , C.WERKS
         , (C.ID_ITEM_NO*10) ID_ITEM_NO
         , C.BSEQ
         , C.IDNRK
         , C.IDNRK_IPRODUCTION
         , C.IDNRK_IDRAWING
         , C.MENGE BPSCP
         , STPO.MENGE  STPO_MENGE
         , C.MEINS MEINS
         , C.IUMS IUMS
         , STPO.MEINS STPO_MEINS
      FROM BOM_CHILDS C
         , XX_MAIN.XX_MAST MAST
         , XX_MAIN.XX_STPO STPO
      WHERE C.MATNR = MAST.MATNR
       AND MAST.STLNR = STPO.STLNR
       AND MAST.STLAN = '1'
       AND MAST.WERKS = C.WERKS
       AND STPO.IDNRK = C.IDNRK
    UNION
    SELECT DISTINCT C.MATNR
         , C.IPRODUCTION
         , C.IDRAWING
         , C.WERKS
         , (C.ID_ITEM_NO*10) ID_ITEM_NO
         , C.BSEQ
         , C.IDNRK
         , C.IDNRK_IPRODUCTION
         , C.IDNRK_IDRAWING
         , C.MENGE BPSCP
         , STPO.MENGE  STPO_MENGE
         , C.MEINS MEINS
         , C.IUMS IUMS
         , STPO.MEINS STPO_MEINS
      FROM BOM_CHILDS C
         , XX_MAIN.XX_MAST MAST
         , XX_MAIN.XX_STPO STPO
      WHERE C.MATNR = MAST.MATNR(+)
       AND MAST.STLNR = STPO.STLNR(+)
       AND MAST.STLAN(+) = '1'
       AND MAST.WERKS(+) = C.WERKS
       AND STPO.IDNRK IS NULL
    ORDER BY MATNR,WERKS,ID_ITEM_NO,BSEQ;

Answer 1

This section is probably causing the problem:

  FROM BOM_CHILDS C
     , XX_MAIN.XX_MAST MAST
     , XX_MAIN.XX_STPO STPO
  WHERE C.MATNR = MAST.MATNR(+)
   AND MAST.STLNR = STPO.STLNR(+)
   AND MAST.STLAN(+) = '1'
   AND MAST.WERKS(+) = C.WERKS
   AND STPO.IDNRK IS NULL

To make this a bit easier, lets rearrange the WHERE clause to order the tables by how they relate:

FROM BOM_CHILDS C
 , XX_MAIN.XX_MAST MAST
 , XX_MAIN.XX_STPO STPO
-- Joining C to MAST
WHERE C.MATNR = MAST.MATNR(+)
  AND C.WERKS = MAST.WERKS(+)
  AND MAST.STLAN(+) = '1'
-- Joining MAST to STPO
  AND MAST.STLNR = STPO.STLNR(+)
  AND STPO.IDNRK IS NULL

---

We have C joined to MAST using C as the "driver" table and picking up data from MAST where it matches (a left join):

FROM BOM_CHILDS C
LEFT JOIN XX_MAIN.XX_MAST MAST
  ON C.MANTR = MAST.MANTR
 AND C.WERKS = MAST.WERKS
 AND MAST.STLAN = '1'

Then we need to add STPO to the joins:

LEFT JOIN XX_MAIN.XX_STPO STPO
  ON MAST.STLNR = STPO.STLNR
 AND STPO.IDNRK IS NULL

---

Putting it all together we get:

FROM BOM_CHILDS C
LEFT JOIN XX_MAIN.XX_MAST MAST
  ON C.MANTR = MAST.MANTR
 AND C.WERKS = MAST.WERKS
 AND MAST.STLAN = '1'
LEFT JOIN XX_MAIN.XX_STPO STPO
  ON MAST.STLNR = STPO.STLNR
 AND STPO.IDNRK IS NULL

---

That said, even though (+) works for left/right/outer joins, Oracle recommends not using it:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax: ...