从Oracle竞争SQL到ANSI SQL [英] Oracle-Conveting SQL to ANSI SQL

问题描述

这与将查询转换为ANSI SQL有关.我尝试使用Oracle旧语法编写此查询，但该查询引发以下错误，因此最终如下所示进行了更改.经过研究，我发现ANSI SQL支持此类要求.

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

这是我编写的有效查询，但很高兴知道是否可以通过ANSI-SQL或使用旧的外部联接语法重写此查询.我正在寻找将联合查询重新编写为with子句中的其他查询的方法.

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;

推荐答案

此部分可能是导致问题的原因:

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

为使此操作更容易些，让我们重新排列WHERE子句以根据它们之间的关系对表进行排序:

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

---

我们已经使用C作为驱动程序"表将C连接到MAST，并从匹配的MAST中拾取数据(左连接):

---

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'

然后我们需要在连接中添加STPO:

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建议您使用FROM子句OUTER JOIN语法，而不要使用Oracle join运算符.使用Oracle连接运算符(+)的外部连接查询受以下规则和限制的约束，这些规则和限制不适用于FROM子句OUTER JOIN语法:...

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: ...

这篇关于从Oracle竞争SQL到ANSI SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！