Where's the Other MBROPT Option?

Published: April 19, 2006

Hey, Ted:

I suppose everybody knows that the Copy File (CPYF) command can copy data from one database file to another. And I suppose everybody knows that, depending on the MBROPT parameter, the data in the source file (the "from" file) can either replace or be added to the data in the target file (the "to" file). Furthermore, I suppose that some people know that the MBROPT(*UPDADD) option causes the system to replace the records that have matching key values and add records with no matching key values. But does anybody know how to add the records that don't have matching key values without updating the records that do?

--Bewildered

You have two methods--one that uses CPYF and one that uses SQL. But first, let's put the problem into context for the readers. Let's start with a file ORDDTL of sales order details. Each line represents an item on the order and is uniquely keyed on order number and line number.

Order     Line  Item  
Number  Number  Number  Quantity
======  ======  ======  ========
    1        1    I01          5  
    1        2    I08          3  
    2        1    I01          6  
    2        2    I09          6  
    3        1    I01          3  
    3        2    I07          3  
    3        3    I09          6  
    3        4    I02          6  
    4        1    I02          5  
    4        2    I08          5  
    4        3    I22          6  
    6        1    I01          8  

Assume a second file ORDDTLCHGS of the same format with additional order lines and/or changes to existing order lines.

Order     Line  Item  
Number  Number  Number  Quantity
======  ======  ======  ========
    5        1    I18          4
    6        1    I01         12
    7        1    I05          6

Notice that two lines, for orders 5 and 7, do not exist in the ORDDTL file. The record for order 6, line 1, contains a new quantity for that order line.

To apply the updates to ORDDTL, use the *UPDADD option, like this:

CPYF FROMFILE(ORDDTLCHGS) TOFILE(ORDDTL) MBROPT(*UPDADD)

Here's the resulting dataset.

Order     Line  Item  
Number  Number  Number  Quantity
======  ======  ======  ========
    1        1    I01          5 
    1        2    I08          3 
    2        1    I01          6 
    2        2    I09          6 
    3        1    I01          3 
    3        2    I07          3 
    3        3    I09          6 
    3        4    I02          6 
    4        1    I02          5 
    4        2    I08          5 
    4        3    I22          6 
    6        1    I01         12 
    5        1    I18          4 
    7        1    I05          6 

Notice that the quantity has changed for order 6, line 1, and the lines for orders 5 and 1 have been added.

Bewildered wants to add 5 and 7 without updating 6. Obviously he needs to add records to the file.

CPYF FROMFILE(ORDDTLCHGS) TOFILE(ORDDTL) MBROPT(*ADD)

However, this CPYF command chokes when it tries to add the 6-1 record to the file. When I tested this example, the system copied the 5-1 record, then canceled, leaving the ORDDTL file only partially updated.

The solution is not in the MBROPT parameter, but in the ERRLVL parameter, which tells CPYF to ignore errors.

CPYF FROMFILE(ORDDTLCHGS) TOFILE(ORDDTL) MBROPT(*ADD) ERRLVL(*NOMAX)

Now the database looks like this:

Order     Line  Item  
Number  Number  Number  Quantity
======  ======  ======  ========
    1        1    I01          5 
    1        2    I08          3 
    2        1    I01          6 
    2        2    I09          6 
    3        1    I01          3 
    3        2    I07          3 
    3        3    I09          6 
    3        4    I02          6 
    4        1    I02          5 
    4        2    I08          5 
    4        3    I22          6 
    6        1    I01          8 
    5        1    I18          4 
    7        1    I05          6 

If you have access to any version of SQL, you may find it to be a better solution.

insert into orddtl                
select *                          
  from orddtlchgs as C            
 where not exists                 
    (select *                     
       from orddtl as OD          
      where C.orderno = OD.orderno
        and C.lineno = OD.lineno)

The parenthesized select looks for at least one record that has the same order number and line number as a record in the ORDDTLCHGS file. If no matching record is found, the command inserts the ORDDTLCHGS record into ORDDTL.

--Ted