Where’s the Other MBROPT Option?

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.

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.

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.