Need help in outer join

sharma03 · #1 (**permalink**) 05-13-11, 07:02

Hi all,

I am new to DB2

I am working on below i need to make outer join in below sql for
AND PE.FBT.CDCO=CO.FVL.VFCO AND PE.FBT.CDUNIT=CO.FVL.VFUNIT

on VFCO & VFUNIT

Can any one please help me in this

SELECT
TRIM(PE.FBT.UNIT),
TRIM(CO.FVL.VFUSE),
TRIM(CO.FVL.VFCAT),
TRIM(CO.FVL.VFMODL),
PE.FBT.CDCLAS,
PE.FBT.CDBDTE
FROM
CO.CUST,
CO.FVL,
PE.FBT,

( select distinct DMDIST, DMRDST from CO.FDSTMST where DMCO in ('HPTL','2000') and DMLOC = '10') DSTMST,
( select distinct PTLPERSONID, CUSTID, DISTRICT from CO.V_SNT_SECURE where CUSTID between '000000' and '999999' or CUSTID = '999999') Secure_M

WHERE
PE.FBT.CDCUST=CO.CUST.CMACCT
AND PE.FBT.CDDIST=CO.CUST.CMDIST

AND ( CO.CUST.CMCUST=(case when (Security_Miles.CUSTID='999999') then CO.CUST.CMCUST else Security_Miles.CUSTID end) )

AND ( CO.CUST.CMDIST=DSTMST.DMDIST )
AND ( DSTMST.DMRDST=(case when (Secure_.DISTRICT in ('0814','9999')) then DSTMST.DMRDST else Secure_M.DISTRICT end) )

AND PE.FBT.CDCO=CO.FVL.VFCO AND PE.FBT.CDUNIT=CO.FVL.VFUNIT

Regards,
Sharma

tonkuma · #2 (**permalink**) 05-13-11, 08:00

Use standard syntax of outer join.

Some DBMS used proprietry syntax for outer join in the past.

But, DB2 supports only standard outer join syntax, like...
tablle-reference LEFT | RIGHT | FULL OUTER JOIN table-reference ON join-condition

sharma03 · #3 (**permalink**) 05-13-11, 08:05

Quote:

Originally Posted by tonkuma

Use standard syntax of outer join.

Some DBMS used proprietry syntax for outer join in the past.

But, DB2 supports only standard outer join syntax, like...
tablle-reference LEFT | RIGHT | FULL OUTER JOIN table-reference ON join-condition

Can you please explain with syntax in the given SQL

tonkuma · #4 (**permalink**) 05-13-11, 08:57

I guessed you can write like this...

Note: I need guess. Because it is not clear which table is outer to what another table.

Code:

...
 FROM  CO.CUST
 INNER JOIN
       PE.FBT
   ON  FBT.CDCUST = CUST.CMACCT
   AND FBT.CDDIST = CUST.CMDIST
 LEFT OUTER JOIN
       CO.FVL
   ON  FBT.CDCO   = FVL.VFCO
   AND FBT.CDUNIT = FVL.VFUNIT 
...

tonkuma · #5 (**permalink**) 05-13-11, 10:01

If my guess for outer join was right, original query might be rewritten like this...

Code:

SELECT
       TRIM(FBT.UNIT)
     , TRIM(FVL.VFUSE)
     , TRIM(FVL.VFCAT)
     , TRIM(FVL.VFMODL)
     , FBT.CDCLAS
     , FBT.CDBDTE
 FROM  CO.CUST
 INNER JOIN
       PE.FBT
   ON  FBT.CDCUST = CUST.CMACCT
   AND FBT.CDDIST = CUST.CMDIST
 LEFT OUTER JOIN
       CO.FVL
   ON  FBT.CDCO   = FVL.VFCO
   AND FBT.CDUNIT = FVL.VFUNIT 
 WHERE 
       EXISTS
       (select 0
         from  CO.V_SNT_SECURE
         where
           (   CUSTID = '999999'
            OR CUSTID = CUST.CMCUST
               AND
               CUSTID between '000000'
                          and '999999'
               /* If CUSTID was CHAR(6) and included only digits, 
                     then this condition would not be necessary. */
           )
           AND DISTRICT
               IN (select DMRDST
                    from  CO.FDSTMST
                    where DMCO   IN ('HPTL','2000')
                      and DMLOC  =   '10'
                      AND DMDIST =   CUST.CMDIST
                   UNION ALL
                   VALUES '0814' , '9999'
                  )
       )
;

tonkuma · #6 (**permalink**) 05-13-11, 21:56

Steps of query rewritings:

(1) Format whole query
and correct incompatible qualifiers, remove unnecessary parentheses or qualifiers.

Code:

SELECT
       TRIM(FBT.UNIT)
     , TRIM(FVL.VFUSE)
     , TRIM(FVL.VFCAT)
     , TRIM(FVL.VFMODL)
     , FBT.CDCLAS
     , FBT.CDBDTE
 FROM
       CO.CUST
     , CO.FVL
     , PE.FBT
     , (select distinct
               DMDIST
             , DMRDST
         from  CO.FDSTMST
         where DMCO in ('HPTL','2000')
           and DMLOC = '10'
       ) DSTMST
     , (select distinct
               PTLPERSONID
             , CUSTID
             , DISTRICT
         from  CO.V_SNT_SECURE
         where CUSTID between '000000'
                          and '999999'
           or  CUSTID = '999999'
       ) Secure_M
 WHERE 
       FBT.CDCUST = CUST.CMACCT
   AND FBT.CDDIST = CUST.CMDIST
   AND CUST.CMCUST
       = case
         when Secure_M.CUSTID = '999999' then
              CUST.CMCUST
         else Secure_M.CUSTID
         end
   AND CUST.CMDIST = DSTMST.DMDIST
   AND DSTMST.DMRDST
       = case
         when Secure_M.DISTRICT in ('0814','9999') then
              DSTMST.DMRDST
         else Secure_M.DISTRICT
         end
/* Begin of outer join conditions */
   AND FBT.CDCO   = FVL.VFCO
   AND FBT.CDUNIT = FVL.VFUNIT 
/*   End of outer join conditions */
;

(2) Rewrite by using new join syntax.

Code:

SELECT
       TRIM(FBT.UNIT)
     , TRIM(FVL.VFUSE)
     , TRIM(FVL.VFCAT)
     , TRIM(FVL.VFMODL)
     , FBT.CDCLAS
     , FBT.CDBDTE
 FROM
       CO.CUST
 INNER JOIN
       PE.FBT
   ON  FBT.CDCUST = CUST.CMACCT
   AND FBT.CDDIST = CUST.CMDIST
 LEFT OUTER JOIN
       CO.FVL
   ON  FBT.CDCO   = FVL.VFCO
   AND FBT.CDUNIT = FVL.VFUNIT
     , (select distinct
               DMDIST
             , DMRDST
         from  CO.FDSTMST
         where DMCO in ('HPTL','2000')
           and DMLOC = '10'
       ) DSTMST
     , (select distinct
               PTLPERSONID
             , CUSTID
             , DISTRICT
         from  CO.V_SNT_SECURE
         where CUSTID between '000000'
                          and '999999'
           or  CUSTID = '999999'
       ) Secure_M
 WHERE
       CUST.CMCUST
       = case
         when Secure_M.CUSTID = '999999' then
              CUST.CMCUST
         else Secure_M.CUSTID
         end
   AND CUST.CMDIST = DSTMST.DMDIST
   AND DSTMST.DMRDST
       = case
         when Secure_M.DISTRICT in ('0814','9999') then
              DSTMST.DMRDST
         else Secure_M.DISTRICT
         end
;

(3) Rewrite WHERE conditions and remove non-used column in sybquery.

Code:

SELECT
       TRIM(FBT.UNIT)
     , TRIM(FVL.VFUSE)
     , TRIM(FVL.VFCAT)
     , TRIM(FVL.VFMODL)
     , FBT.CDCLAS
     , FBT.CDBDTE
 FROM
       CO.CUST
 INNER JOIN
       PE.FBT
   ON  FBT.CDCUST = CUST.CMACCT
   AND FBT.CDDIST = CUST.CMDIST
 LEFT OUTER JOIN
       CO.FVL
   ON  FBT.CDCO   = FVL.VFCO
   AND FBT.CDUNIT = FVL.VFUNIT
     , (select distinct
               DMDIST
             , DMRDST
         from  CO.FDSTMST
         where DMCO in ('HPTL','2000')
           and DMLOC = '10'
       ) DSTMST
     , (select distinct
            --   PTLPERSONID
               CUSTID
             , DISTRICT
         from  CO.V_SNT_SECURE
         where CUSTID between '000000'
                          and '999999'
           or  CUSTID = '999999'
       ) Secure_M
 WHERE
   (   Secure_M.CUSTID = '999999'
    OR
       Secure_M.CUSTID = CUST.CMCUST
   )
   AND CUST.CMDIST = DSTMST.DMDIST
   AND
   (   Secure_M.DISTRICT in ('0814','9999')
    OR
       Secure_M.DISTRICT = DSTMST.DMRDST
   )
;

(4)
4-1) Make DSTMST as subquery of Secure_M.
Assumed no more than one row of DSTMST would match in final result rows.
4-2) Rewrite WHERE conditions in Secure_M.

Code:

SELECT
       TRIM(FBT.UNIT)
     , TRIM(FVL.VFUSE)
     , TRIM(FVL.VFCAT)
     , TRIM(FVL.VFMODL)
     , FBT.CDCLAS
     , FBT.CDBDTE
 FROM
       CO.CUST
 INNER JOIN
       PE.FBT
   ON  FBT.CDCUST = CUST.CMACCT
   AND FBT.CDDIST = CUST.CMDIST
 LEFT OUTER JOIN
       CO.FVL
   ON  FBT.CDCO   = FVL.VFCO
   AND FBT.CDUNIT = FVL.VFUNIT
     , LATERAL
       (select
            --   distinct
            --   CUSTID
            -- , DISTRICT
               0
         from  CO.V_SNT_SECURE Secure_M
             , LATERAL
               (select distinct
                    --   DMDIST
                       DMRDST
                 from  CO.FDSTMST
                 where DMCO in ('HPTL','2000')
                   and DMLOC = '10'
                   AND DMDIST = CUST.CMDIST
               ) DSTMST
         where 
           (   CUSTID = '999999'
            OR
               CUSTID = CUST.CMCUST
               AND
               CUSTID between '000000'
                          and '999999'
           )
           AND DISTRICT in (  '0814' , '9999'
                            , DSTMST.DMRDST
                           )
       ) Secure_M
-- WHERE
;

(5) Move Secure_M to WHERE clause by using EXISTS predicate.

Code:

SELECT
       TRIM(FBT.UNIT)
     , TRIM(FVL.VFUSE)
     , TRIM(FVL.VFCAT)
     , TRIM(FVL.VFMODL)
     , FBT.CDCLAS
     , FBT.CDBDTE
 FROM
       CO.CUST
 INNER JOIN
       PE.FBT
   ON  FBT.CDCUST = CUST.CMACCT
   AND FBT.CDDIST = CUST.CMDIST
 LEFT OUTER JOIN
       CO.FVL
   ON  FBT.CDCO   = FVL.VFCO
   AND FBT.CDUNIT = FVL.VFUNIT
 WHERE
       EXISTS
       (select
               0
         from  CO.V_SNT_SECURE Secure_M
             , LATERAL
               (select distinct
                    --   DMDIST
                       DMRDST
                 from  CO.FDSTMST
                 where DMCO in ('HPTL','2000')
                   and DMLOC = '10'
                   AND DMDIST = CUST.CMDIST
               ) DSTMST
         where 
           (   CUSTID = '999999'
            OR
               CUSTID = CUST.CMCUST
               AND
               CUSTID between '000000'
                          and '999999'
           )
           AND DISTRICT
               IN (  '0814' , '9999'
                   , DSTMST.DMRDST
                  )
       ) -- Secure_M
;

(6) Move DSTMST into IN predicate.

Code:

SELECT
       TRIM(FBT.UNIT)
     , TRIM(FVL.VFUSE)
     , TRIM(FVL.VFCAT)
     , TRIM(FVL.VFMODL)
     , FBT.CDCLAS
     , FBT.CDBDTE
 FROM
       CO.CUST
 INNER JOIN
       PE.FBT
   ON  FBT.CDCUST = CUST.CMACCT
   AND FBT.CDDIST = CUST.CMDIST
 LEFT OUTER JOIN
       CO.FVL
   ON  FBT.CDCO   = FVL.VFCO
   AND FBT.CDUNIT = FVL.VFUNIT
 WHERE
       EXISTS
       (select 0
         from  CO.V_SNT_SECURE Secure_M
         where 
           (   CUSTID = '999999'
            OR
               CUSTID = CUST.CMCUST
               AND
               CUSTID between '000000'
                          and '999999'
           )
           AND DISTRICT
               IN (select DMRDST
                    from  CO.FDSTMST
                    where DMCO   IN ('HPTL','2000')
                      and DMLOC  =   '10'
                      AND DMDIST =   CUST.CMDIST
                   -- ) DSTMST
                   UNION ALL
                   VALUES '0814' , '9999'
                  )
       )
;

sharma03 · #7 (**permalink**) 05-17-11, 04:02

Thanks for your help in re writing the whole query

I will execute the query and let you know the output

Regards
Sharma

Internet Services

Web Development

Digital Marketing

Consumer Tech