RE: [EXTERNAL] Re: adding primary keys to a newly created table

Hi Carel
First of all – thanks for your reply
After reading some of the results of my google search and the replies that I received, I am going in a different direction
Running an SQL to determine if there are any duplicates (of the fields considered to be the primary keys), and capturing those records with those duplicate keys
Then running a separate SQL to delete all but one record of those duplicates (based upon the relative record numbers – keeping the record with the lowest relative record number)


Alan Shore
Solutions Architect
IT Supply Chain Execution

[cid:image001.png@01D72184.4E0159B0]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx

‘If you're going through hell, keep going.’
Winston Churchill

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Carel
Sent: Thursday, March 25, 2021 2:28 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Re: adding primary keys to a newly created table

You could do a ADDPFCST afterwards.

Or a 2-step SQL script using RUNSQLSTM (if possible).


Op 25-3-2021 om 16:30 schreef Alan Shore via MIDRANGE-L:

Hi everyone
We are on V7r3
We are creating number of extracts to transfer over to another system
The extracts are created in the following manner (This is just a simple example)

CREATE OR REPLACE TABLE LIBRARY/OUTFILE AS
(SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT
FROM INFILE
WHERE SOURCE = '04')
WITH DATA
ON REPLACE DELETE ROWS

We have now been asked to ensure certain fields within these tables are unique
For example, in the above SOURCE and ORDNO
I was looking into PRIMARY KEYS for this
Here are my questions that I am trying to google search on
Is there a way of including the primary keys as part of the above create?
What happens if a duplicate key is found? - will the process stop, or is there a way to code this so that duplicates are bypassed - but I can be informed that duplicates exist?

My Google search has not yet come up with anything definitive that answers the above questions, but my search continues

Alan Shore
Solutions Architect
IT Supply Chain Execution

[cid:image003.png@01D7216A.4107F710]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx<mailto:ASHORE@xxxxxxxxxxxxxxxxxxxx>

'If you're going through hell, keep going.'
Winston Churchill



The Nature’s Bounty Co. is now The Bountiful Company! Our email address will be changing from @nbty.com to @bountifulcompany.com. Please update your email address book so that important communications are not interrupted.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com<https://amazon.midrange.com>



The Nature’s Bounty Co. is now The Bountiful Company! Our email address will be changing from @nbty.com to @bountifulcompany.com. Please update your email address book so that important communications are not interrupted.