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

Hi Rob
Thanks for both of your replies
This latest one is what I am leaning towards

Alan Shore
Solutions Architect
IT Supply Chain Execution

[cid:image001.png@01D72175.327CD110]

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 Rob Berendt
Sent: Thursday, March 25, 2021 12:40 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] Re: adding primary keys to a newly created table

I suspect some wizard will replace my example with something better but here goes.
With T1 as(
Select PrimaryKeyOne, PrimaryKeyTwo, count(*)
From myTable
Group by PrimaryKeyOne, PrimaryKeyTwo
Having count(*)>1)
Select PrimaryKeyOne, PrimaryKeyTwo, ...
From myTable
Where (PrimaryKeyOne, PrimaryKeyTwo) in (
Select t1.PrimaryKeyOne, t1.PrimaryKeyTwo
From T1)

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com<http://www.dekko.com>

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Alan Shore via MIDRANGE-L
Sent: Thursday, March 25, 2021 12:32 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Cc: Alan Shore <ashore@xxxxxxxx<mailto:ashore@xxxxxxxx>>
Subject: RE: [EXTERNAL] Re: adding primary keys to a newly created table

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Thanks for your reply Charles
The more Im looking into this – the more I believe primary keys are not the way to go
Maybe a separate SQL obtaining counts of records – grouped by the fields that are considered primary fields and if there are any such records – send an e-mail with the duplicate keys and delete records from the file that have duplicate keys, but the later relative record number (I hope this last piece makes some sense)

Alan Shore
Solutions Architect
IT Supply Chain Execution

[cid:image001.png@01D72172.D93DBFD0]

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

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, March 25, 2021 12:27 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: [EXTERNAL] Re: adding primary keys to a newly created table

In one statement? No.

You could run an ALTER TABLE after the extract...but in that case, if you
replaced the table that already had a PK constraint, then you'd have to
handle the error thrown when trying to ADD a pk constraint.

Charles

On Thu, Mar 25, 2021 at 9:30 AM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx%3cmailto:midrange-l@xxxxxxxxxxxxxxxxxx>>> wrote:

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<mailto:ASHORE@xxxxxxxxxxxxxxxxxxxx%3cmailto: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<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx%3cmailto: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><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<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx%3cmailto: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><https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx%3cmailto: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><https://amazon.midrange.com<https://amazon.midrange.com>>

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx%3cmailto: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><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<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx%3cmailto: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><https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx%3cmailto: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><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.
--
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>
--
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.