Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    4

    Unanswered: Need help in outer join

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  3. #3
    Join Date
    May 2011
    Posts
    4

    Need Help in SQL

    Quote Originally Posted by tonkuma View Post
    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 
    ...
    Last edited by tonkuma; 05-13-11 at 10:05. Reason: Add note.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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'
                      )
           )
    ;
    Last edited by tonkuma; 05-14-11 at 05:55. Reason: Replace IN predicate. Because it doesn't allow mix of expressions(include constant) and full-select.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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'
                      )
           )
    ;
    Last edited by tonkuma; 05-14-11 at 05:54. Reason: Replace IN predicate.Because it doesn't allow mix of expressions(including constant) and full-select.

  7. #7
    Join Date
    May 2011
    Posts
    4

    Need help in outer join

    Thanks for your help in re writing the whole query

    I will execute the query and let you know the output


    Regards
    Sharma

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •