Re: SQL reference file -- MIDRANGE-L

Constraints now are part of the CREATE TABLE statement. No need for ALTER TABLE. It is also possible to use a LIKE <table or view> clause, in addition to the AS <select-statement> form the OP presented. And at least since or at 7.2, if there is a REFFLD the table, that is copied in, it appears from current documentation.

I recommend reading the newest (at least 7.2) doc on CREATE TABLE and see what might help.

There will be things that might not work - fields defined with date types like *USA, etc., are converted to *ISO, I saw - but someone else please verify, this was just a __really__ cursory look at this.

Regards
Vern

On Tue, 2 Apr, 2024 at 8:31 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

To: midrange systems technical discussion

I think the pure SQL Solution would be to use user defined (weak) types...

create type addressline as varchar(50) with weak type rules;

WITH WEAK TYPE RULES

Specifies that weak typing rules are used for operations where this data
type is an operand including
assignments, comparisons, and function resolution. When values of a weakly
typed distinct type are
used, the data type is effectively treated as the specified built-in-type
when processing the operation.

create table customers (
...
addressline1 addressline,
addressline2 addressline,
...
);

But I've never actually done so.
Honestly, I seem to recall some issues about compatibility.
But the "weak type rules" which I don't recall, would seem to alleviate
those concerns.

HTH,
Charles

On Tue, Apr 2, 2024 at 5:53 AM Brian Garland via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>> wrote:

I found an old thread in the archives about replicating DDS reference file
functionality.

CREATE TABLE xxxx AS (SELECT col1, col2, ... FROM ref_table)...

I have done this in the past but I was never fond of it because I felt the
code was complicated and not always clear for a couple of reasons:
a) "One-off" fields had to be defined with a CAST statement or all fields
needed to be in your reference table
b) Constraints (including primary key) had to be done as an ALTER TABLE.

Have any improvements been made around this topic? Is there any tooling
that helps with creating table definitions based on reference columns?

Thanks,
Brian

--
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or company to whom they are
addressed.
Do not disclose, distribute, or copy this email to others outside your
company. If you have received this email in error, please notify the
sender
immediately and delete this email from your system.
--
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
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.

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