Re: SQL Create Table AS with primary key

[K Crawford <kscx3ksc@xxxxxxxxx>]

The Alter Table never crossed my mind.  That will work also.
I hoped to do it in the create table to eliminate the second script.
I will use the Alter Table.

On Tue, Aug 2, 2022 at 11:33 AM Hiebert, Chris <chris.hiebert@xxxxxxxxxxxxxx>
wrote:

> ALTER TABLE myLib.ResultTb  ADD PRIMARY KEY (ColA, ColB);
>
> Chris Hiebert
> Senior Programmer/Analyst
> Disclaimer: Any views or opinions presented are solely those of the author
> and do not necessarily represent those of the company.
>
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of K
> Crawford
> Sent: Tuesday, August 2, 2022 9:10 AM
> To: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> Subject: SQL Create Table AS with primary key
>
>
>
> I have an SQL statement like this, I want to add a key to the table
>
>
>
> CREATE TABLE myLib.ResultTbl AS
>
>   (
>
>    Select t1.ColA, t1.ColB, t1.ColC, t2.ColA, t1.ColD
>
>    from myLib.Table1 t1
>
>    join myLib.Table2 t2
>
>      on t1.ColA = t2.ColA and t1.ColB = t2.ColB
>
>    where t1.ColA = 208
>
>      and t1.ColR = 'Y'
>
>      and t1.ColB in
>
>             (
>
>              Select t1b.ColB
>
>              from myLib.Table1 t1b
>
>              where t1b.ColA = 208
>
>                and t1b.ColR != 'Y'
>
>                and t1b.ColM = 2023
>
>             )
>
>   )
>
> with data
>
>
>
> I want to add a key to the result table myLib.ResultTbl.
>
> I have tried adding 'primary key (t1.ColA, t1.ColB)' but not working.
>
> Is my only option to follow this create with a create index?
>
>
>
> --
>
> KCrawford
> --
> 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