Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2014
    Posts
    7

    Unanswered: need to include 2 columns from new table in an existing query

    We have an existing query which is used to extract data, Now we need to include 2 new columns from a new tables.

    columns to be included in query: WORK_NUMBER. CONTRACT_NUMBER
    table name : BMSIW.BMSIW_NON_FINANCIAL
    coulmn used from above table to meet condition with exisitng table in the query: OPPURTUNITY_ID

    one of the column in existing table in querycan be used to meet the condition
    (OPP.OPP_NUMBER in the query)

    Requirement is to get get the data for WORK_NUMBER. CONTRACT_NUMBER, which is matching with the OPP.OPP_NUMBER (we have similar data for BMSIW.BMSIW_NON_FINANCIAL.OPPUTUNITY_ID = OPP.OPP_NUMBER)

    Need help to include this columns and the posibilities to fetch data
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so, what seems to be the problem ? have you tried yourself a solution ?
    there is an sql primer in the db2 library that could help..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    What Guy is getting at is this forum is to help people with specific question or issue, not do their job or homework for them. Though some of us will do the work for you, please jost post to whom we do our billing(name, address, telephone, etc...).
    Dave

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please don't mix traditional join syntax(using comma and WHERE condition) and newer join syntax(using JOIN keyword and ON condition).

    First, rewrite traditional join syntax
    Code:
    ...
    FROM 
      QAADMIN.FLT_COUNTRY                                     CTY
    , QAIWGBS.QAV_WB_OPPTY                                    OPP
    , QAADMIN.DIM_IOT                                         DIM
    , QAIWGBS.QAV_WB_REVIEW                                   REV
    RIGHT OUTER JOIN QAADMIN.V_DUPLICATE_REVIEWS_MQT          AL5
    ON  REV.QAWB_ID              = AL5.QAWB_ID
    AND REV.DATA_SOURCE          = AL5.DS
    LEFT OUTER JOIN QAVIEWS.V_ROLE_REVIEWER                   ROL 
      ON  REV.QAWB_ID     = ROL.QAWB_ID
      AND REV.DATA_SOURCE = ROL.DS
    ...
    WHERE 
        REV.OPPTY_QAWB_ID          = OPP.QAWB_ID 
    AND REV.DATA_SOURCE            = OPP.DATA_SOURCE 
    AND CTY.WB_COUNTRY             = OPP.LEAD_COUNTRY 
    AND CTY.IW_ISO                 = DIM.ISO_CODE 
    ...
    to newer join syntax like...
    Code:
    ...
     FROM
           QAIWGBS.QAV_WB_REVIEW                     REV
     INNER JOIN
           QAIWGBS.QAV_WB_OPPTY                      OPP
      ON   OPP.QAWB_ID        = REV.OPPTY_QAWB_ID
      AND  OPP.DATA_SOURCE    = REV.DATA_SOURCE 
     INNER JOIN
           QAADMIN.FLT_COUNTRY                       CTY
      ON   CTY.WB_COUNTRY     = OPP.LEAD_COUNTRY 
     INNER JOIN
           QAADMIN.DIM_IOT                           DIM
      ON   DIM.ISO_CODE       = CTY.IW_ISO
     RIGHT OUTER JOIN
           QAADMIN.V_DUPLICATE_REVIEWS_MQT           AL5
      ON   AL5.QAWB_ID        = REV.QAWB_ID
      AND  AL5.DS             = REV.DATA_SOURCE
     LEFT OUTER JOIN
           QAVIEWS.V_ROLE_REVIEWER                   ROL 
      ON   ROL.QAWB_ID        = REV.QAWB_ID
      AND  ROL.DS             = REV.DATA_SOURCE
    ...
    Then, try to add join for new table(BMSIW.BMSIW_NON_FINANCIAL).
    Last edited by tonkuma; 04-27-14 at 00:58. Reason: Remove extra "INNER JOIN" keyword after "QAADMIN.FLT_COUNTRY CTY". Add part of WHERE conditions.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another my issue was your RIGHT OUTER JOIN might be replaced by an EXISTS predicate, like

    Code:
     FROM
    ...
    /*
    RIGHT OUTER JOIN QAADMIN.V_DUPLICATE_REVIEWS_MQT          AL5
    ON  REV.QAWB_ID              = AL5.QAWB_ID
    AND REV.DATA_SOURCE          = AL5.DS
    */
    ...
    
     WHERE
    ...
    /*
    AND AL5.DUPLICATE              = 'N'
    */
    
      AND  EXISTS
           (SELECT 0
             FROM  QAADMIN.V_DUPLICATE_REVIEWS_MQT   AL5
             WHERE AL5.QAWB_ID   = REV.QAWB_ID
              AND  AL5.DS        = REV.DATA_SOURCE
              AND  AL5.DUPLICATE = 'N'
           )
    Last edited by tonkuma; 04-27-14 at 00:54. Reason: Add FROM and WHERE keywords.

Posting Permissions

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