Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2014
    Posts
    3

    Unanswered: Merge - Row Not Found For Merge

    I'm running db2 on an iSeries running 7r1m0. My understanding is that the version of DB2 is tied to that; if a more specific version is needed I can't quite figure out how to get it. The sticky thread suggest the db2level command but I couldn't figure out where or how to run that successfully on the iSeries.

    In any case, I have a merge statement that I can't seem to figure out. What I'm attempting to do is straight forward I think: update the record if it exists and insert it if it does not. The first part, the update, works, but the insert does not. I'm trying to do this by dynamically calculating the query and running it via ODBC but no error is returned. So I tried running it directly on the iSeries' interactive SQL program and it returns "Row Not Found For MERGE" and, critically, does not actually insert the record.

    I'm not sure if that response is actually an error or not ... the record in question wouldn't be in the database and thus not found. That's kind of the point, it needs to be added but the query just doesn't seem to want to do so.

    I've tried many different permutations but here's the query as it is now:
    Code:
    MERGE INTO BBH400.TMSDTAR73.SAHM00P As t
    USING (SELECT SMCLVL, SMILVL, SMI, SMCN, SMCL4, SMSNS, SMSLRT, SMYEAR, SMLTYP
    		FROM  BBH400.TMSDTAR73.SAHM00P
    		WHERE SMCLVL='TB' AND SMILVL='T1' AND SMCN = '**********' AND SMCL4 ='AMZ' AND SMI='123456789' AND SMSNS=1 AND SMYEAR=2012 AND SMSLRT=1 AND SMLTYP='N' ) as s
    ON s.SMCLVL=t.SMCLVL AND s.SMILVL=t.SMILVL AND s.SMCN=t.SMCN AND s.SMCL4=t.SMCL4 AND s.SMI=t.SMI AND s.SMSNS=t.SMSNS AND s.SMYEAR=t.SMYEAR AND s.SMSLRT=t.SMSLRT AND s.SMLTYP=t.SMLTYP
    
    WHEN MATCHED THEN
    	UPDATE SET (SMQ01, SMQ02, SMQ03, SMQ04, SMQ05, SMQ06, SMV01, SMV02, SMV03, SMV04, SMV05, SMV06) = ( 36,70,161,108,126,38,79.0416,153.692,353.4916,237.1248,276.6456,83.4328)
    	
    WHEN NOT MATCHED THEN
          INSERT ( SMCLVL, SMILVL, SMCL1, SMCL2, SMCL3, SMCL4, SMCL5, SMCN, SMIL1, SMIL2, SMIL3, SMIL4, SMIL5, SMI, SMSDV, SMSNS, SMTOS, SMSLRP, SMYEAR, SMCUR, SMSLRT, SMLTYP, SMRTYP, SMQ01, SMQ02, SMQ03, SMQ04, SMQ05, SMQ06, SMQ07, SMQ08, SMQ09, SMQ10, SMQ11, SMQ12, SMV01, SMV02, SMV03, SMV04, SMV05, SMV06, SMV07, SMV08, SMV09, SMV10, SMV11, SMV12, SMT01, SMT02, SMT03, SMT04, SMT05, SMT06, SMT07, SMT08, SMT09, SMT10, SMT11, SMT12, SMC01, SMC02, SMC03, SMC04, SMC05, SMC06, SMC07, SMC08, SMC09, SMC10, SMC11, SMC12 )
    	  VALUES ( s.SMCLVL, s.SMILVL, '***','***','***',s.SMCL4,'***',s.SMCN,'***','***','***','***','***',s.SMI,'***',s.SMSNS,'**','******',s.SMYEAR, 'U', s.SMSLRT , s.SMLTYP,'A',36,70,161,108,126,38,193,95,6,114,40,1,79.0416,153.692,353.4916,237.1248,276.6456,83.4328,423.7508,208.582,13.1736,250.2984,87.824,2.1956,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
    I've tried using the actual values for the insert rather than referring to the source but that didn't make a difference.

    The contextual help for that response just says:
    -- If this is an UPDATE, INSERT, DELETE, or MERGE statement, no rows satisfy the subselect, WHERE clause, or MERGE criteria. No rows were updated, inserted, or deleted.

    I'd be grateful for any ideas/suggestions,
    Bryan

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) Why source table(as s) and target table(As t) are same?
    All ON predicates look like "equal to same column"(e.g. s.SMCLVL=t.SMCLVL, so on...).
    So, all rows of "s" must match with row(s) of "t".


    (2) You wrote
    "The first part, the update, works, but the insert does not."
    How did you confirmed that fact?


    (3) Was the using subquery returned any row?
    Try
    Code:
    /*USING (*/SELECT SMCLVL, SMILVL, SMI, SMCN, SMCL4, SMSNS, SMSLRT, SMYEAR, SMLTYP
    		FROM  BBH400.TMSDTAR73.SAHM00P
    		WHERE SMCLVL='TB' AND SMILVL='T1' AND SMCN = '**********'
    		  AND SMCL4 ='AMZ'AND SMI='123456789' AND SMSNS=1
    		  AND SMYEAR=2012AND SMSLRT=1 AND SMLTYP='N' /*) as s*/

  3. #3
    Join Date
    Sep 2014
    Posts
    3
    Thanks for the reply tonkuma, much appreciated. Maybe I'm misunderstanding how to use the merge call in my situation or maybe it's just not applicable at all.

    In my case I'm working from an outside data source/application/system and trying to either update or insert records on our iSeries over ODBC using dynamically created queries. The source is really an external source and cannot be queried from the iSeries.

    So, if I gather correctly, the problem here is that since the using subquery doesn't find anything it's not going to look into the source to see if that record is there and then follow the matched/not matched logic. That would explain what I'm seeing at least.

    (1) They are the same because of the misunderstanding above, I think. In any case, the true source of the data is external to the iSeries and I can't query it. I'm trying to see if there's a record in that table. If there is, update it using values from the external data source. If there isn't, then insert it using values from the external database. I'm trying to avoid doing separate calls if possible hence my attempt at using merge.

    (2) I confirmed the update worked by finding a record that existed in both my external data source and the target. When I run the query the target is updated. I then found a record that existed in the external source but not in the target. When I run the query it succeeds but does not insert the row. I'm confirming the results by querying the target table afterwards.

    (3) Depends (see above). In some cases, the using subquery finds a row and in other cases it does not. My misunderstanding, I think, is that I had though that if the using subquery didn't find anything, the NOT MATCHED logic would be followed. However, it would seem that if the subquery finds nothing, nothing will happen because there's nothing to compare to the source.

    I tried the changes you suggested and got the following error:
    Keyword SELECT not expected. Valid tokens: USING

    So any ideas if/how a merge query can be using in this situation?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I don't know how to handle the external source on iSeries.
    So, I have no idea (at least now).


    (3) Depends (see above). In some cases, the using subquery finds a row and in other cases it does not. My misunderstanding, I think, is that I had though that if the using subquery didn't find anything, the NOT MATCHED logic would be followed. However, it would seem that if the subquery finds nothing, nothing will happen because there's nothing to compare to the source.
    You are right!

  5. #5
    Join Date
    Sep 2014
    Posts
    3
    Thanks again tonkuma. If nothing else, at least I know I'm not crazy.

Posting Permissions

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