Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    24

    Unanswered: Help needed on a DB2 query

    i have this scenario, for which i have written the sql statements as shown below

    This query will return combination of ITEM_OBJID, S_ITEMI, S_IDCNG from Object_Data and Part_list table.

    SELECT OBJECT_DATA.ITEM_OBJID, S_ITEMI, S_IDCNG FROM SN.OBJECT_DATA AS OBJECT_DATA INNER JOIN SN.PART_LIST AS PART_LIST ON OBJECT_DATA.ITEM_OBJID = PART_LIST.ITEM_OBJID WHERE OBJECT_DATA.S_DC='RD'

    3. For each S_ITEMI, S_IDCNG from above query we will check if this combination exists in PM. If below query returns the values then the combination does exists.

    3.a We query for ITM_ID, EC_MC_ID from PM using Join on three tables AVNTMITEM, AVENTACI, AVNTEC as below.

    SELECT ITM_ID, EC_MC_ID FROM SN.AVNTMITEM AVNTMITEM JOIN SN.AVENTACI AVENTACI ON AVNTMITEM.OID=AVENTACI.OID_MITEM JOIN SN.AVNTEC AVNTEC ON AVNTEC.OID=AVENTACI.OID_EC WHERE ITM_ID = ‘S_ITEMI’ AND EC_MC_ID = ‘IDCNG’


    3.b If we get ITM_ID, EC_MC_ID from above query we will update the Q_PMSYNC as 'Y' in ERE Object_Data table

    UPDATE SN.OBJECT_DATA SET Q_PMSYNC = 'Y' WHERE ITEM_OBJID = ‘ITEM_OBJID’ AND S_IDCNG = ‘S_IDCNG’


    I tried doing whole of the work using a single sql statement which got complex and i landed no where, this task can also be accomplished usinga Stored procedure.

    can any one help me writing a single sql statement for this task as it will be faster to execute as the records may be in lacs.

    Thanks,
    Prashant

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You should be able to achieve this with a single MERGE statement; check the manual for syntax.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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