Thread: Help needed on a DB2 query
03-18-09, 15:49 #1Registered User
- Join Date
- Mar 2009
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.
03-18-09, 16:05 #2:-)
Provided Answers: 1
- Join Date
- Jun 2003
- Toronto, Canada
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.