× 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.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.