Re: SQL Insert question

Yes, so the whole point is not having to explicitly specify each column on
the insert from ICPRTMIA to AUDIA

ICPRTMIA AUDIA
IACOM# IACOM#
IAPRT# IAPRT#
IA101 IA101
etc... etc..
IAUSRN
IAUSER
IATRND
IATRT
IAPRCF IAPRCF
IAVIAC IAVIAC
IAHCD1 IAHCD1
<end> <end>

So I think I understand some of the suggestions... (I don't understand the
"temporal-tables / history tables" mentioned by Daniel).

These files each have over 100 fields... the AUDIA table that I'm
inserting into has 5 more fields (user, user id, date, time, transaction
code) that are not at the end.
I tried to create two data structures as Darryl suggested, but the
compiler didn't like that (I didn't qualify them)... so the fields are the
same name.

dcl-ds PartMaster extname('ICPRTMIA') inz;
end-ds;

dcl-ds PartAudit extname('AUDIA') inz;
end-ds;

Compiler error for every field:
RNF3804: Name IAWEBF in external description AUDIA is not renamed;
external name is ignored.

Am I defining these incorrectly?

Buck, I'm not understanding how a stored procedure would make this any
easier - I'm already in an RPG program that is called from the CGI. But I
would definitely place this into an RPG service program so I could call it
from other places. This is why I didn't want to use native access to write
the AUDIA.

Thanks,
Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel
Gross
Sent: Tuesday, May 9, 2023 5:06 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Insert question

Hi Greg,

what kind of columns are those in the audit table?

Maybe the temporal-tables / history tables / versioning feature is already
enough.

Another method is using an INSERT view on the table, which only consists
of the same columns as the original - the audit columns would need good
default values for this to work.

A bit more information would be good - so one can think of a specific
solution.

HTH
Daniel

Am 09.05.2023 um 22:51 schrieb Greg Wilburn <

gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

I have two files/tables that are nearly identical. They are:

1. Part Master file
2. Part Master Audit file

The Audit file contains 5 additional fields that are not found in the

Part Master. All of the other fields in the file are identical (mirror
images of each other in name and attributes), and are in the same order
EXCEPT for the last ten fields in the Audit file. The Audit file contains
the 5 additional fields inserted just before the last 5 matching fields.

With RPG, I could simply chain (or read) the Part Master record, eval

the 5 additional field values, and then just write the Part Master Audit
record.

I'm looking for an "elegant" way to do the same thing with SQL without

using native access. This will be running in a CGI job.

Any ideas?
TIA
[Logo]<https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx

1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.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.

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

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