|
--+-(----+-*column-definition*----------------------+-+--)-+----->| +-LIKE--+-*table-name*-+--+--------------+-+ |
Yes. They are called "constraints". For common CL command outfiles it's
as simple as ADDPFCST. For SQL you look at the constraint clause on CREATE
TABLE.
Run SQL Scripts allow you to build your own templates. Here is one
template I have
-- category: My really cool examples
-- description: A pretty Create or Replace Table
CREATE OR REPLACE TABLE MyBigTableName for system name BigTable
(
column1 INTEGER NOT NULL, -- Desc column 1
column2 VARCHAR(100) ALLOCATE(20) -- Desc column 2
);
-- Here's another sample. This table was a lookup table. For example,
what's the last journal sequence number we looked at?
-- As a war on 5250 tools the "short names" are obscured.
set current schema = 'GDIHQ';
CREATE OR REPLACE TABLE Lookup_table for system name T00000001
(
Lookup_item for column C000000001 VARCHAR(100) ALLOCATE(20)
not null constraint Lookup_table_primary_key PRIMARY KEY, --
Item to lookup
Lookup_value_character for column C000000002 VARCHAR(100) ALLOCATE(10)
with default null, -- Value of item (if character)
Lookup_value_integer for column C000000003 bigint with default null,
-- Value of item (if integer)
Lookup_value_decimal for column C000000004 decimal (25, 5) with
default null, -- Value of item (if decimal)
Lookup_value_timestamp for column C000000005 timestamp with default
null, -- Value of item (if timestamp)
CONSTRAINT Lookup_table_has_a_value CHECK ( -- One, and only one, of
the lookup result columns will have a value.
(Lookup_value_character is not null and lookup_value_integer is null
and Lookup_value_decimal is null and Lookup_value_timestamp is null) or
(Lookup_value_character is null and lookup_value_integer is not null
and lookup_value_decimal is null and Lookup_value_timestamp is null) or
(Lookup_value_character is null and lookup_value_integer is null and
lookup_value_decimal is not null and Lookup_value_timestamp is null) or
(Lookup_value_character is null and lookup_value_integer is null and
lookup_value_decimal is null and Lookup_value_timestamp is not null))
)
RCDFMT T00000001R
;
insert into lookup_table (lookup_item, lookup_value_integer)
values('JOURNAL_SEQUENCE_NUMBER', 1);
insert into lookup_table (lookup_item, lookup_value_timestamp)
values('USER_CHANGE_TIMESTAMP', CURRENT TIMESTAMP - 1000 years);
select * from lookup_table;
Being the <expletive deleted> I am I obfuscated the column names and stuff
to discourage the use of 5250 functions to get file information.
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
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Alan Shore via MIDRANGE-L
Sent: Thursday, March 25, 2021 11:30 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: 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.
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
'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
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
