Re: SQL reference file

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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