7 Replies Latest reply: Feb 28, 2011 2:00 AM by Etbin RSS

    Help Required in full outer Join

    680725
      I feel the below query can be changed to full outer join. But, I've not been able to do so.
      I require your help in changing it to full outer join. My current query is
      SELECT CLAIMNO,'1' INDX FROM D_CLAIM@CMS2PROD
      WHERE clntsys=76500 and facility=76501 and filecreatedt='18-feb-2011' and fileupdatedt is null
      MINUS
      SELECT CLAIMNO,'1' FROM D_CLAIM
      WHERE clntsys=76500 and facility=76501 and filecreatedt='18-feb-2011' and fileupdatedt is null
      UNION
      SELECT CLAIMNO,'2' FROM D_CLAIM
      WHERE clntsys=76500 and facility=76501 and filecreatedt='18-feb-2011' and fileupdatedt is null
      MINUS
      SELECT CLAIMNO,'2' FROM D_CLAIM@cms2prod
      WHERE clntsys=76500 and facility=76501 and filecreatedt='18-feb-2011' and fileupdatedt is null
        • 1. Re: Help Required in full outer Join
          Charles Hooper
          donisback wrote:
          I feel the below query can be changed to full outer join. But, I've not been able to do so.
          I require your help in changing it to full outer join. My current query is
          SELECT CLAIMNO,'1' INDX FROM D_CLAIM@CMS2PROD
          WHERE clntsys=76500 and facility=76501 and filecreatedt='18-feb-2011' and fileupdatedt is null
          MINUS
          SELECT CLAIMNO,'1' FROM D_CLAIM
          WHERE clntsys=76500 and facility=76501 and filecreatedt='18-feb-2011' and fileupdatedt is null
          UNION
          SELECT CLAIMNO,'2' FROM D_CLAIM
          WHERE clntsys=76500 and facility=76501 and filecreatedt='18-feb-2011' and fileupdatedt is null
          MINUS
          SELECT CLAIMNO,'2' FROM D_CLAIM@cms2prod
          WHERE clntsys=76500 and facility=76501 and filecreatedt='18-feb-2011' and fileupdatedt is null
          I do not think that query is doing what you think that it is doing - on first inspection, it was not doing what I thought either due to the order in which the UNION and MINUS operators are performed. If the FILECREATEDT column is defined as a DATE, your query is relying on an implicit date conversion - in such a case, you should replace:
          filecreatedt='18-feb-2011'
          With:
          filecreatedt=TO_DATE('18-feb-2011','DD-MON-YYYY')
          Now, building a simple model to explain what is happening, we create two tables with 10 rows each. The first table has C1 values that increase by 2, while the second has C1 values that increase by 3:
          CREATE TABLE T1 AS
          SELECT
            ROWNUM*2 C1,
            TRUNC(SYSDATE)+ROWNUM*2 C2
          FROM
            DUAL
          CONNECT BY
            LEVEL<=10;
           
          CREATE TABLE T2 AS
          SELECT
            ROWNUM*3 C1,
            TRUNC(SYSDATE)+ROWNUM*3 C2
          FROM
            DUAL
          CONNECT BY
            LEVEL<=10;
          It appears that the intended result of your query is to obtain a distinct list of those rows that are in each table that are not in both tables - the UNION ALL minus the intersection of the rows in two tables. However, that is not what you are achieving with that query. To demonstrate, the two halves of your query:
          SELECT
            C1,
            C2
          FROM
            T1
          MINUS
          SELECT
            C1,
            C2
          FROM
            T2;
           
          C1 C2
          -- ---------
           2 28-FEB-11
           4 02-MAR-11
           8 06-MAR-11
          10 08-MAR-11
          14 12-MAR-11
          16 14-MAR-11
          20 18-MAR-11
           
          SELECT
            C1,
            C2
          FROM
            T2
          MINUS
          SELECT
            C1,
            C2
          FROM
            T1;
           
          C1 C2
          -- ---------
           3 01-MAR-11
           9 07-MAR-11
          15 13-MAR-11
          21 19-MAR-11
          24 22-MAR-11
          27 25-MAR-11
          30 28-MAR-11
          As can be seen by the above, each half returned 7 rows - there are 7 rows in each table that is not in the other table. Common sense would state that if we UNION these two results (assuming no duplicate values in each table), we would see 14 rows:
          SELECT
            C1,
            C2
          FROM
            T1
          MINUS
          SELECT
            C1,
            C2
          FROM
            T2
          UNION
          SELECT
            C1,
            C2
          FROM
            T2
          MINUS
          SELECT
            C1,
            C2
          FROM
            T1;
           
          C1 C2
          -- ---------
           3 01-MAR-11
           9 07-MAR-11
          15 13-MAR-11
          21 19-MAR-11
          24 22-MAR-11
          27 25-MAR-11
          30 28-MAR-11
          Only 7 rows? Let's try again with the help of inline views to control the order in which the MINUS and UNION operators are processed:
          SELECT
            *
          FROM
            (SELECT
              C1,
              C2
            FROM
              T1
            MINUS
            SELECT
              C1,
              C2
            FROM
              T2)
          UNION
          SELECT
            *
          FROM
            (SELECT
              C1,
              C2
            FROM
              T2
            MINUS
            SELECT
              C1,
              C2
            FROM
              T1);
           
          C1 C2
          -- ---------
           2 28-FEB-11
           3 01-MAR-11
           4 02-MAR-11
           8 06-MAR-11
           9 07-MAR-11
          10 08-MAR-11
          14 12-MAR-11
          15 13-MAR-11
          16 14-MAR-11
          20 18-MAR-11
          21 19-MAR-11
          24 22-MAR-11
          27 25-MAR-11
          30 28-MAR-11
          Note that the above returned 14 rows. We can do the same using just two outer joins:
          SELECT
            T1.C1,
            T1.C2
          FROM
            T1,
            T2
          WHERE
            T1.C1=T2.C1(+)
            AND T2.C1 IS NULL
          UNION
          SELECT
            T2.C1,
            T2.C2
          FROM
            T1,
            T2
          WHERE
            T2.C1=T1.C1(+)
            AND T1.C1 IS NULL;
            
          C1 C2
          -- ---------
           2 28-FEB-11
           3 01-MAR-11
           4 02-MAR-11
           8 06-MAR-11
           9 07-MAR-11
          10 08-MAR-11
          14 12-MAR-11
          15 13-MAR-11
          16 14-MAR-11
          20 18-MAR-11
          21 19-MAR-11
          24 22-MAR-11
          27 25-MAR-11
          30 28-MAR-11
          Or we can do it with a full outer join and a MINUS operator:
          SELECT
            NVL(T1.C1,T2.C1) C1,
            NVL2(T1.C1,T1.C2,T2.C2) C2
          FROM
            T1 FULL OUTER JOIN T2
              ON T1.C1=T2.C1
          MINUS
          SELECT
            T1.C1,
            T1.C2
          FROM
            T1,
            T2
          WHERE
            T1.C1=T2.C1;
           
          C1 C2
          -- ---------
           2 28-FEB-11
           3 01-MAR-11
           4 02-MAR-11
           8 06-MAR-11
           9 07-MAR-11
          10 08-MAR-11
          14 12-MAR-11
          15 13-MAR-11
          16 14-MAR-11
          20 18-MAR-11
          21 19-MAR-11
          24 22-MAR-11
          27 25-MAR-11
          30 28-MAR-11
          Or just with a full outer join with a WHERE clause:
          SELECT
            NVL(T1.C1,T2.C1) C1,
            NVL2(T1.C1,T1.C2,T2.C2) C2
          FROM
            T1 FULL OUTER JOIN T2
              ON T1.C1=T2.C1
          WHERE
            (T1.C1 IS NULL
              OR T2.C1 IS NULL);
           
          C1 C2
          -- ---------
           3 01-MAR-11
           9 07-MAR-11
          15 13-MAR-11
          21 19-MAR-11
          24 22-MAR-11
          27 25-MAR-11
          30 28-MAR-11
           8 06-MAR-11
          20 18-MAR-11
           2 28-FEB-11
          10 08-MAR-11
           4 02-MAR-11
          14 12-MAR-11
          16 14-MAR-11
          With the above knowledge, you should be able to fix your SQL statement to produce the expected results.

          Charles Hooper
          Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
          http://hoopercharles.wordpress.com/
          IT Manager/Oracle DBA
          K&M Machine-Fabricating, Inc.
          • 2. Re: Help Required in full outer Join
            680725
            Thanks a lot Charles for clarifying.
            But,I am still unable to get the right query. Here's the query I wrote. Request you to correct it.
            The below query returns no rows, but there are rows infact in the tables.:
            SELECT NVL(D.CLAIMNO,DT.CLAIMNO) CLAIMNO,
            NVL2(D.CLAIMNO,'PROD','STAGING') SOURCE
            FROM D_CLAIM@CMS2PROD D FULL OUTER JOIN
             D_CLAIM DT
             ON  D.CLAIMNO=DT.CLAIMNO
             WHERE 
            D.CLNTSYS=76500 AND D.FACILITY=76501 AND DT.CLNTSYS=D.CLNTSYS AND DT.FACILITY=D.FACILITY
            AND (D.CLAIMNO IS NULL OR DT.CLAIMNO IS NULL)
            • 3. Re: Help Required in full outer Join
              680725
              My requirement is -
              I have a table D_CLAIM in staging.
              I have a similar table D_CLAIM in production.

              Now, I want to find the claims which are not in sync, that is, the claims could be in staging but not in production and vice versa.
              I want to verify this where clntsys=76500 and facility=76501

              Now, the below query is fetching all the clntsys and facility, Request your help in framing the right query:
              SELECT NVL(D.CLAIMNO,DT.CLAIMNO) CLAIMNO,nvl(d.clntsys,dt.clntsys) CLNTSYS,
              NVL2(D.CLAIMNO,'PROD','STAGING') SOURCE
              FROM D_CLAIM@CMS2PROD D FULL OUTER JOIN
              ( SELECT CLNTSYS,CLAIMNO FROM  D_CLAIM WHERE CLNTSYS=76500 AND FACILITY=76501) 
              DT
               ON  D.CLAIMNO=DT.CLAIMNO
              -- AND D.CLNTSYS=76500 AND D.FACILITY=76501 AND DT.CLNTSYS=D.CLNTSYS AND DT.FACILITY=D.FACILITY
               WHERE 
              (D.CLAIMNO IS NULL OR DT.CLAIMNO IS NULL)
              • 4. Re: Help Required in full outer Join
                680725
                Hi Everyone,
                Could somebody help me in framing the right full outer join query?
                Else, could somebody point to some resource explaining the same?

                I couldn't find examples for full outer join with where clause included, i.e, I want to selectively compare the two tables.

                In my case , I want to compare the table D_CLAIM where clntsys=76500 and facility=76501 with D_CLAIM@cms2prod where clntsys=76500 and facility=76501. I don't want to compare for other clntsys or facility.
                • 5. Re: Help Required in full outer Join
                  Etbin
                  Something like:
                  select nvl(p.claimno,s.claimno) claimno,
                         nvl2(p.claimno,'PROD','STAGING') the_source,
                         :the_clntsys clntsys,
                         :the_facility facility
                    from (select claimno
                            from d_claim
                           where clntsys = :the_clntsys
                             and facility = :the_facility
                         ) p
                         full outer join
                         (select claimno
                            from d_claim@cms2prod        /* never tried with remote */
                           where clntsys = :the_clntsys
                             and facility = :the_facility
                         ) s
                         on p.claimno = s.claimno
                   where p.claimno is null
                      or s.claimno is null
                  Regards

                  Etbin
                  • 6. Re: Help Required in full outer Join
                    680725
                    Thanks Etbin. Your query works perfectly fine.
                    My query now is:
                    select nvl(p.claimno,s.claimno) claimno,
                           nvl2(p.claimno,'STAGING','PROD') the_source,
                            nvl(p.iuflag,s.iuflag) iuflag,
                            nvl(p.filecreatedt,s.filecreatedt) filecreatedt
                     from (select claimno,iuflag, filecreatedt,fileupdatedt
                              from d_claim
                             where clntsys = 76500
                               and facility = 76501
                                 and iuflag<>'D'
                           ) p
                           full outer join
                           (select claimno,iuflag, filecreatedt,fileupdatedt
                              from d_claim@cms2prod
                             where clntsys = 76500
                               and facility = 76501
                           ) s
                           on p.claimno = s.claimno
                     where p.claimno is null
                        or s.claimno is null
                    Instead of having inline queries in the from clause, why can't I add clntsys and facility in the where clause.
                    When I add clntsys and facility in the where clause it's not giving me any results. Could you please explain the reason?
                    • 7. Re: Help Required in full outer Join
                      Etbin
                      Instead of having inline queries in the from clause, why can't I add clntsys and facility in the where clause.
                      When I add clntsys and facility in the where clause it's not giving me any results. Could you please explain the reason? >
                      It's the so called princple of conservation being in action here:
                      a well known old saying from around here states (avoiding vulgarity) *No matter how you turn, you back is always behind*
                      Regarding conditions I think not exists(...) must be used as there are no rows in the other table but then there would be no need to use outer join you'd ask for:
                      select key,... 
                        from prod p 
                       where ... and ... and not exists(select null from staging where key = p.key and ... and ...)
                      union all
                      select key,... 
                        from staging s 
                       where ... and ... and not exists(select null from prod where key = s.key and ... and ...)
                      Regards

                      Etbin