6 Replies Latest reply on Jan 24, 2019 7:45 AM by RBVP

    SQL - OUTER APPLY

    RBVP

      Hello,

      Would be able to help with my issue?

       

      I have query build for Microsoft SQL Server Management Studio 17  and I am currently rewriting it to SQL Developer V3.2.2, but I am facing a problem with "OUTER APPLY " ( and some others)

      Data extracted is from JDE 8.12

       

      Please see below my query for SQL Developer:

       

      SELECT t.TDCO, t.TDDGL, t.TDMCU, t.TDOBJ, t.TDSUB, t.TDAN8, AIAC09, ABAC15, t.TDDCTO, t.TDDOCO, t.TDICUT, t.TDITM, t.TDTXA1, AITXA1, t.TDTXR1, gl.GLEXR, t.TDSTAM/100 AS TDSTAM, gl.GLDOC, (gl.GLMCU||'.'||gl.GLOBJ||'.'||gl.GLSUB) AS GLANI,gl.GLAA/100 AS GLAA

      FROM

      (SELECT TDCO, TDAN8, TDDCTO, TDDOCO, TDITM, TDTXA1, TDTXR1, TDMCU, TDOBJ, TDSUB, TDAEXP, TDSTAM, TDCRCD, TDICUT, TDDGL, TDUSER

      FROM proddta.F0018

      WHERE TDCO= '22222' AND

            ((LTRIM(TDMCU)= '33333'AND TDDCTO NOT IN  ('H1','H2','H3','H4','H5')) OR

            (LTRIM(TDMCU)= '22222' AND TDDCTO IN  ('H1','H2','H3','H4','H5'))) AND

            TDSTAM <>'0' AND

            TDDGL > '117275') t

      LEFT JOIN proddta.F03012 ON AICO=t.TDCO AND AIAN8=t.TDAN8

      LEFT JOIN proddta.F0101 ON ABAN8=t.TDAN8

       

       

      OUTER APPLY

      (SELECT GLCO, GLDOC, CONVERT(INT,substr(RTRIM(GLEXR),8)) AS GLEXR, GLEXA, GLMCU, GLOBJ, GLSUB, GLDGJ, GLAA

           FROM proddta.F0911

           WHERE GLCO= '22222' AND

                 GLOBJ = '212xxxx' AND GLLT = 'AA' AND

                 GLDCT = 'JE' AND GLPOST = 'P' AND

                 GLDGJ >'117275' AND CONVERT(INT,substr(RTRIM(GLEXR),8))=t.TDDOCO AND

                 GLOBJ=t.TDOBJ) gl WHERE gl.GLEXR IS NULL;

       

       

      Would you know how cold I rewriting the below code for SQL Developer ?

       

       

      OUTER APPLY

      (SELECT GLCO, GLDOC, CONVERT(INT,substr(RTRIM(GLEXR),8)) AS GLEXR, GLEXA, GLMCU, GLOBJ, GLSUB, GLDGJ, GLAA

           FROM proddta.F0911

           WHERE GLCO= '33333' AND

                 GLOBJ = '212xxxx' AND GLLT = 'AA' AND

                 GLDCT = 'JE' AND GLPOST = 'P' AND

                 GLDGJ >'117275' AND CONVERT(INT,substr(RTRIM(GLEXR),8))=t.TDDOCO AND

                 GLOBJ=t.TDOBJ) gl WHERE gl.GLEXR IS NULL;

        • 1. Re: SQL - OUTER APPLY
          thatJeffSmith-Oracle

          you need it for Oracle Database, SQL Developer is just the client tool (like SSMS is for SQL Server)

           

          version 3.2 is old, VERY old - upgrade if you can - it's free!

           

          where is the original query?

           

          what exactly is the problem you're having?

           

          SQL Developer has a translator that will take SQL Server code to Oracle code...

          • 2. Re: SQL - OUTER APPLY
            RBVP

            "version 3.2 is old, VERY old - upgrade if you can - it's free!"

            OK. I will try it later =) . Thanks a lot to inform me that I have very old version

             

             

            "where is the original query?"

            The original query was build in my excel ( Data +  From Data Base + From SQL Server Data Base)  .

             

            "SQL Developer has a translator that will take SQL Server code to Oracle code"

            I tried, but didn't translate very well

             

            what exactly is the problem you're having?

            (if I replace OUTER APPLY by LEFT JOIN,  I would have error on line 20: Column 14 which is "WHERE gl.GLEXR is null" )

             

                       13-    LEFT JOIN

                       14-    (SELECT GLCO, GLDOC, TO_NUMBER (INT,substr(RTRIM(GLEXR),8)) AS GLEXR, GLEXA, GLMCU, GLOBJ, GLSUB, GLDGJ, GLAA

                       15-  FROM proddta.F0911

                       16-    WHERE GLCO= '33333' AND

                       17-    GLOBJ = '212xxx' AND GLLT = 'AA' AND

                       18 - GLDCT = 'JE' AND GLPOST = 'P' AND

                       19-  GLDGJ >'117275' AND TO_NUMBER (INT,substr(RTRIM(GLEXR),8))=t.TDDOCO AND

                        20- GLOBJ=t.TDOBJ)gl

                        21-  WHERE gl.GLEXR is null;

            • 3. Re: SQL - OUTER APPLY
              RBVP

              correct: "I would have error on line 21: Column 14"

              • 4. Re: SQL - OUTER APPLY
                Glen Conway

                Check out the explainer on OUTER APPLY (equivalent to a variant of LEFT OUTER JOIN) in

                https://oracle-base.com/articles/12c/lateral-inline-views-cross-apply-and-outer-apply-joins-12cr1#cross-apply-join

                 

                and also an Ask Tom discussion on how Oracle came to add this Microsoft created syntax to Oracle 12c:

                https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9530807800346558418

                • 5. Re: SQL - OUTER APPLY
                  thatJeffSmith-Oracle

                  if you share the original sql, we can test the translator...

                  • 6. Re: SQL - OUTER APPLY
                    RBVP

                    the original query SELECT t.TDCO, t.TDDGL, t.TDMCU, t.TDOBJ, t.TDSUB, t.TDAN8, AIAC09, ABAC15, t.TDDCTO, t.TDDOCO, t.TDICUT, t.TDITM, t.TDTXA1, AITXA1, t.TDTXR1, gl.GLEXR, t.TDSTAM/100 AS TDSTAM, gl.GLDOC, gl.GLMCU+'.'+gl.GLOBJ+'.'+gl.GLSUB AS GLANI, gl.GLAA/100 AS GLAA FROM (SELECT TDCO, TDAN8, TDDCTO, TDDOCO, TDITM, TDTXA1, TDTXR1, TDMCU, TDOBJ, TDSUB, TDAEXP, TDSTAM, TDCRCD, TDICUT, TDDGL, TDUSER FROM F0018 WHERE TDCO= '3333' AND       ((LTRIM(TDMCU)= '22333'AND TDDCTO NOT IN  ('AAA','BBB')) OR       (LTRIM(TDMCU)= '3333' AND TDDCTO IN  ('AAA','BBB'))) AND       TDSTAM <>'0' AND       TDDGL > '117275') t LEFT JOIN stage.dbo.F03012 ON AICO=t.TDCO AND AIAN8=t.TDAN8 LEFT JOIN Stage.dbo.F0101 ON ABAN8=t.TDAN8 OUTER APPLY (SELECT GLCO, GLDOC, CONVERT(INT,RIGHT(RTRIM(GLEXR),8)) AS GLEXR, GLEXA, GLMCU, GLOBJ, GLSUB, GLDGJ, GLAA     FROM stage.dbo.F0911     WHERE GLCO= '22333' AND           GLOBJ = '210000' AND GLLT = 'AA' AND           GLDCT = 'JE' AND GLPOST = 'P' AND           GLDGJ >'117275' AND CONVERT(INT,RIGHT(RTRIM(GLEXR),8))=t.TDDOCO AND           GLOBJ=t.TDOBJ) gl WHERE gl.GLEXR IS NULL