Re: SQL Create Or Replace with keyed Fiile

Hi,

alternatively to Jim’s idea, don’t use CREATE OR REPLACE at all - just „INSERT“ the lines into the destination file - then the keys should be there.

The problem with create or replace is, that it will do exactly that - and a SELECT has no „key“ (or better index) - it’s just a SELECT.

With DDS-vs-DDL it’s best, to use only one.

Regards,
Daniel

Von meinem iPhone gesendet



Von meinem iPhone gesendet

Am 10.12.2022 um 16:59 schrieb Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>:

Why use CRTDUPOBJ? Just used SQL to create the file and populate it at the same time. Set the key fields in SQL as well.

Alternatively create an index over the qtemp objects

Jim Oberholtzer
Agile Technology Architects

On Dec 10, 2022, at 9:53 AM, gio.cot via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx> wrote:
Hi all
I have a phisical file with two key fields created from DDS for compile my
RPG program; this file is then duplicated with CRTDUPOBJ to Qtemp and then
via "SQL Create or Replace", populated with records/rows, but in this way
(SQL Create or Replace) i lost the key fields; the file became a nokeyed
file !!
Crtdupobj
create or Replace table qtemp.oc32W AS (
WITH pr AS ( bla , bla , bla )
with data
ON REPLACE DELETE ROWS
Is there any way to avoid it (lost key fields ??)
Thanks in advance
Gio
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-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 RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-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