×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
On 23-Oct-2014 10:56 -0500, Darryl Freinkel wrote:
I need some help on this one.
I am merging 5 company records into 1 file and need to drop any
duplicates.
<<SNIP>>
What alternative ways are there to achieve this merge or what
mistake am I not seeing?
<<SNIP>>
The MERGE statement might be appropriate for the scenario.?
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/sqlp/rbafymerge.htm>
_Merging data_
"Use the MERGE statement to conditionally insert, update, or delete rows
in a table or view.
You can use the MERGE statement to update a target table from another
table, a derived table, or any other table-reference. This other table
is called the source table. The simplest form of a source table is a
list of values.
..."
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzmerge.htm>
_MERGE_
"The MERGE statement updates a target (a table or view) using data from
a source (result of a table reference). Rows in the target that match
the input data may be updated or deleted as specified, and rows that do
not exist in the target may be inserted as specified. ...
..."
Or perhaps the CPYF command might be appropriate for the scenario.?
If the required uniqueness is enforced with unique keys, then the Copy
File (CPYF) command offers a Member Option (MBROPT) of Update\Add
(*UPDADD) that, IIRC, replaces a duplicate row with the /same/ record in
the From File (FROMFILE) member (FROMMBR).
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/dm/rbal3updadd.htm>
_Specifying *UPDADD when copying files_
"When you specify *UPDADD on the Copy File (CPYF) command, a from-file
key value builds before the from-file record moves into the to-file. The
from-file builds this key value by using the key specifications of the
to-file.
Before the key value is built, the system performs any necessary field
or data mapping, data conversion, or record selection. The system checks
the to-file to see if this key value already exists in it (duplicate key
of the from-file data). If the key value does exist in the to-file, the
from-file record that contains the key value updates that to-file record.
The following rules apply if you specify MBROPT(*UPDADD) on the CPYF
command:
• The to-file must be a local database physical file that contains a
primary or unique key.
• ...
..."
FWiW: I did not spend much time reviewing the messages in this thread
due to the apparent guessing, required presumably as a side effect of
too little information having been provided about the scenario; no DDL
nor constraints, and lack of any example data and both the improper
results seen and the expected results.
As an Amazon Associate we earn from qualifying purchases.