2 Replies Latest reply: Apr 1, 2013 6:20 AM by Frank Kulash RSS

    converting oracle join to Ansi sql join

    1000257
      Hi Guys,
      I am new to SQL and trying to convert the following Oracle query (joins) into ANSI sql joins...Can someone please help me?

      SELECT M.EXTERNALCODE, M.NAME AS MNAME, SC.BIRIM, SM.TRANSACTIONDATE, SMD.AMOUNT,
      SMD.UNITPRICE, SM.ID AS SMID, SMD.ID AS SMDID, F.NAME AS FNAME,
      IFNULL (SMD.AMOUNT, 0, SMD.AMOUNT) * IFNULL (SMD.UNITPRICE, 0, SMD.UNITPRICE) AS TOTALPRICE, SMD.AMOUNT AS RECEIVED_QUANTITY,
      PD.ORDERID, PD.AMOUNT QUANTITY, PO.PROCESSDATE
      FROM STOCKMAINTRANSACTION SM,
      STOCKMAINTRANSACTIONDETAIL SMD,
      MATERIAL M,
      STOCKCARD SC,
      FVSTOCK FVS,
      FIRM F,
      PURCHASEORDER PO,
      PURCHASEORDERDETAIL PD,
      PURCHASEORDERDETAILSUPPLIED PDS
      WHERE SM.ID = SMD.MAINTRANSACTIONID
      AND SMD.MATERIALID = M.ID
      AND SMD.STOCKCARDID = SC.ID
      AND SM.PROPREF = FVS.RECORDID(+)
      AND FVS.FIELDID(+) = 2559
      AND FVS.FLEVEL(+) = 'F'
      AND F.ID(+) = SUBSTR (FVS.FVALUE, 1, 9)
      AND SM.TRANSDEFID in (999,2329,2344,2370,150000903,150005362)
      AND SMD.CANCELLED = 0
      AND SMD.STOCKUPDATED = 1
      AND SMD.ID = PDS.STOCKMAINTRANSACTIONDETAILID
      AND PDS.ORDERDETAILID = PD.ORDERDETAILID
      AND PO.ORDERID = PD.ORDERID
      AND (M.ID = {@MATERIALID@} OR {@MATERIALID@} = 0)
      AND (SM.STOREID = {@STOREID@} OR {@STOREID@} = 0)
      AND (F.ID = {@SUPPLIERID@} OR {@SUPPLIERID@} = 0)
      AND SM.TRANSACTIONDATE BETWEEN {@STARTDATE@} AND {@ENDDATE@}
      ORDER BY F.NAME, M.EXTERNALCODE, SM.TRANSACTIONDATE


      Really appreciate the help!
      Thanks.
        • 1. Re: converting oracle join to Ansi sql join
          S10390
          Check these links
          http://www.citagus.com/citagus/blog/ansi-join-vs-oracle-traditional-join/
          http://www.oracle-developer.net/display.php?id=213
          • 2. Re: converting oracle join to Ansi sql join
            Frank Kulash
            Hi,

            Welcome to the forum!

            To convert to ANSI syntax, replace join conditions in the WHERE clause
            FROM           x
            ,             y
            WHERE         x.x1  = y.y1
            AND           x.x2  = y.y2
            with ON conditions in the FROM clause:
            FROM           x
            JOIN             y   ON    x.x1  = y.y1
                                     AND   x.x2  = y.y2
            In inner joins, conditions that do not reference 2 tables are not really join conditions, so it doesn't matter if they are in the FROM clause or in the WHERE clause.
            In your case
            SM.TRANSDEFID in (999,2329,2344,2370,150000903,150005362)
            could be part of a join condition involving sm, or it could be in the WHERE clause. Most people find it clearer if 1-table conditions like this are in the WHERE clause.
            Again, this only applies to inner joins. For outer joins, all conditions that apply to a table that may lack matching rows must be included in the FROM clause, like this:
            LEFT OUTER JOIN  fvstock   fvs  ON   sm.propref       = fvs.recordid
                                            AND  fvs.fieldid  = 2559
                                AND  fvs.flevel   = 'F'
            Try it.
            If you have trouble, post your best attempt, along with CREATE TABLE and INSERT statements for a little sample data from all the tables involved, and the results you want from that data. Simplify the problem. Post only the tables and columns that you don't know how to handle.
            See the forum FAQ {message:id=9360002}
            user8428528 wrote:
            ...
            AND (M.ID = {@MATERIALID@} OR {@MATERIALID@} = 0)
            AND (SM.STOREID = {@STOREID@} OR {@STOREID@} = 0)
            AND (F.ID = {@SUPPLIERID@} OR {@SUPPLIERID@} = 0)
            AND SM.TRANSACTIONDATE BETWEEN {@STARTDATE@} AND {@ENDDATE@}
            This is not valid Oracle SQL. Is {@MATERIALID@} some kind of variable?