Re: ExternalRe: SQL Inserting into file based on records not in primary file
Marco Facchinetti <marco.facchinetti-kthxv0ud/[email protected]> Fri, 20 Feb 2026 18:27:42 +0100
| Newsgroups | gmane.comp.lang.as400.rpg |
|---|---|
| Message-ID | <CAMsgu4r97ymUXOShXvc-2UrrVz+KAegJsWEEva6CDiCjhk=Yew@mail.gmail.com> |
Hi Jeryy, this code can be helpfull while doing utility services on Db
(sorry about italian comments):
Dcl-Proc RegSql;
Dcl-Pi *n opdesc;
StrSql varchar(4000) const;
End-Pi;
Dcl-s xSqe varchar(200) dim(*auto:200);
Dcl-s i int(10);
Dcl-s p int(10);
Dcl-s c1 int(10);
Dcl-s eS varchar(32000);
// Recupera i diagnostici in una stringa unica omnicomprensiva.
xSqlCod = SqlCod; // Altrimenti lo modifica.
EXEC SQL GET DIAGNOSTICS :eS = ALL;
// Il log delle Fetch deve essere chiesto esplicitamente
if DebugDta.Fetch = *blanks and
(%scan('COMMAND_FUNCTION=DYNAMIC FETCH':eS) > 0 or
%scan('COMMAND_FUNCTION=FETCH':eS) > 0);
Return;
EndIf;
// Scrive lo statement (O quanto passato se non deriva da una
stringa)
for i = 1 to 10;
p21msg = %subst(StrSql:1 + ((i - 1) * %len(p21msg)));
exsr Salta;
if 1 + i * %len(p21msg) > %len(StrSql);
leave;
EndIf;
EndFor;
// Separa i diagnostici e li ordina
xSqe = %split(eS:';');
sorta xSqe;
clear p21msg;
for i = 1 to %elem(xSqe);
// Esclude quelli non significativi.
p = %scan('=':xSqe(i));
if p > 0 and
%subst(xSqe(i):1: p - 1) IN %LIST('DB2_NUMBER_CONNECTIONS'
:'CLASS_ORIGIN'
:'DB2_MODULE_DETECTING_ERROR'
:'MESSAGE_LENGTH'
:'MESSAGE_OCTET_LENGTH'
:'SERVER_NAME'
:'SUBCLASS_ORIGIN'
:'CONNECTION_NAME'
:'DB2_AUTHORIZATION_ID'
:'DB2_CONNECTION_METHOD'
:'DB2_CONNECTION_NUMBER'
:'DB2_CONNECTION_STATE'
:'DB2_CONNECTION_TYPE'
:'DB2_PRODUCT_ID'
:'DB2_SERVER_CLASS_NAME'
:'DB2_SERVER_NAME'
);
iter;
EndIf;
// Li stampa su tre colonne
c1 += 1;
%subst(p21msg:1 + (c1 - 1) * 66:66) = xSqe(i);
if c1 = 3;
exsr Salta;
clear p21msg;
clear c1;
EndIf;
EndFor;
if p21msg <> *blanks;
exsr Salta;
EndIf;
// Separatore istruzioni.
p21msg = *all'-';
write ZC0612P;
Return;
Begsr Salta;
if *in98;
write ZC0611P;
*in98 = *off;
EndIf;
write ZC0612P;
EndSr;
End-Proc;
A R ZC0611P
A TEXT('Intestazione stampa')
A SKIPB(002)
A SPACEA(001)
A UTENTE 10A O 2
A +36
A 'Duplicazione azienda log Sql,
orig-
A ine'
A P0ORI 1A O +1
A +1
A 'Destinazione:'
A P0DES 1A O +1
A +38
A DATE(*YY)
A EDTCDE(Y)
A +1
A TIME
A +31
A 'Pag.'
A +0
A PAGNBR
A EDTCDE(Z)
A 1
A
'-----------------------------------
A
------------------------------------
A
------------------------------------
A
------------------------------------
A
------------------------------------
A ------------------------'
A SPACEB(001)
A 1
A 'Statement Sql eseguito ed
esiti'
A SPACEB(001)
A 1
A
'-----------------------------------
A
------------------------------------
A
------------------------------------
A
------------------------------------
A
------------------------------------
A ------------------------'
A SPACEB(001)
A R ZC0612P
A SPACEB(001)
A P21MSG 198 O 1
--
Marco Facchinetti
Mr S.r.l.
Tel. 035 962885
Cel. 393 9620498
Skype: facchinettimarco
Il giorno ven 20 feb 2026 alle ore 17:49 Jerry Forss <[email protected]>
ha scritto:
> This looks promising, like Marco's.
>
> I do need it to be dynamic as the file/library can vary.
>
> -----Original Message-----
> From: RPG400-L <rpg400-l-bounces-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> On Behalf Of Eric
> Wesson
> Sent: Friday, February 20, 2026 10:47 AM
> To: RPG programming on IBM i <[email protected]>
> Subject: ExternalRe: SQL Inserting into file based on records not in
> primary file
>
> You could use:
> Exec sql
> create table Purgetable as(
> select * from Library.File
> where not exists (Select 1 from PrimaryFile
> where
> PrimaryFile.Keyfield = File.Keyfield
> ) with data
>
>
> ________________________________
> From: RPG400-L <rpg400-l-bounces-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> on behalf of Marco
> Facchinetti <marco.facchinetti-kthxv0ud/[email protected]>
> Sent: Friday, February 20, 2026 10:41 AM
> To: RPG programming on IBM i <[email protected]>
> Subject: Re: SQL Inserting into file based on records not in primary file
>
> Hi Jerry, I don't know what's wrong but I would use:
>
> create table myNEWtable as(select * from myOLDtable where theField =
> 'whatever') with data
>
> HTH
> --
> Marco Facchinetti
>
> Mr S.r.l.
>
> Tel. 035 962885
> Cel. 393 9620498
>
> Skype: facchinettimarco
>
>
> Il giorno ven 20 feb 2026 alle ore 17:32 Jerry Forss <[email protected]>
> ha scritto:
>
> > What I want to do is get all the records in a file that do not have a
> > match on a field in a primary file.
> > That work.
> >
> > Then I want to copy those records into a purge file before I actually
> > delete them.
> > That is failing.
> >
> > Here is the code. Just testing so it's messy.
> > **Free
> > Ctl-opt option(*SrcStmt : *NoDebugIO)
> > Dftactgrp(*NO)
> > ActGrp(*Caller);
> >
> > //
> > ======================================================================
> > ==============
> > // Files
> > //
> > ======================================================================
> > ==============
> >
> >
> > //
> > ======================================================================
> > ==============
> > // Copy Source
> > //
> > ======================================================================
> > ==============
> >
> >
> > //
> > ======================================================================
> > ==============
> > // Data Structures
> > //
> >
> ====================================================================================
> > dcl-ds RecLayout extname('AMFLIBA/MBALREP');
> > end-ds ;
> > //
> > ======================================================================
> > ==============
> > // Global Variables
> > //
> > ======================================================================
> > ==============
> >
> > Dcl-S IncludeRecords Char(200);
> > Dcl-S SQLSelect Char(200);
> > Dcl-S InsertSQL Char(200);
> > Dcl-S File Char(10);
> > Dcl-S Library Char(10);
> > Dcl-S PurgeLibrary Char(10);
> > Dcl-S Done Ind Inz('0');
> > Dcl-S False Ind Inz('0');
> > Dcl-S True Ind Inz('1');
> > Dcl-C SqlOK Const('00000');
> > Dcl-C SqlCmpError Const('01557');
> > Dcl-C SqlTruncate Const('01004');
> >
> > //
> > ======================================================================
> > ==============
> > // Prototypes
> > //
> > ======================================================================
> > ==============
> >
> >
> > //
> > ======================================================================
> > ==============
> > // Input Parameters
> > //
> > ======================================================================
> > ==============
> >
> >
> > //
> > ======================================================================
> > ==============
> > // Mainline
> > //
> > ======================================================================
> > ==============
> >
> > Exec sql SET OPTION CLOSQLCSR=*ENDMOD ;
> >
> > PurgeLibrary = 'PL20260220';
> > Library = 'AMFLIBA';
> > File = 'MBALREP';
> > IncludeRecords = 'alcucd < 0 And alcucd Not In (Select s2cucd From
> > amfliba/mbs2rep';
> >
> > // Check If File Is Locked
> > SqlSelect = 'SELECT * ' +
> > 'From ' + %Trim(Library) + '/' + %Trim(File) + ' ' +
> > 'Where ' + %Trim(IncludeRecords) + ')';
> >
> > // Prepare Sql Statement
> > Exec SQL Prepare CopyCmd From :SqlSelect;
> >
> > // Declare Sql Cursor
> > Exec SQL Declare Csr10 Cursor For CopyCmd;
> >
> > // Run Sql Statement
> > Exec SQL Open Csr10;
> >
> > // Process Data Set
> > DoU Done;
> >
> > // Get Employee Data
> > Exec Sql Fetch Next From Csr10 Into : RecLayout;
> >
> > // If Eof Of Data or Unknown Error get out
> > If (SQLSTT <> SQLOK)
> > And (SQLSTT <> SQLCmpError);
> > Leave;
> > EndIf;
> >
> > InsertSQL = 'Insert Into ' + %Trim(PurgeLibrary) + '/' +
> > %Trim(File) + ' ' +
> > 'Values(?)';
> >
> > // Prepare Sql Statement
> > Exec SQL Prepare InsertCmd From :InsertSql;
> >
> > // Insert into target table
> > Exec Sql Execute InsertCmd;
> >
> > EndDo;
> >
> > // Close cursor
> > Exec Sql
> > Close Csr10;
> >
> >
> > *InLr = True;
> > Return;
> >
> > It is giving the error
> > Additional Message Information
> >
> > Message ID . . . . . . : SQL0117
> > Date sent . . . . . . : 02/20/26 Time sent . . . . . . :
> > 10:09:51
> >
> > Message . . . . : Statement contains wrong number of values.
> >
> > Cause . . . . . : One of the following conditions may exist:
> > -- For an INSERT or UPDATE statement, the number of values is not
> > the same
> > as the number of columns.
> > -- For an UPDATE statement, the number of entries in the select
> > list of a
> > row fullselect does not match the number of columns listed in the SET
> > clause.
> > -- For an INSERT with subselect, the number of entries in the
> > select list
> > is not the same as the number of columns for the INSERT.
> > -- For an INSERT statement, one or more of the columns omitted
> > from the
> > column list was created as NOT NULL.
> > -- For an INSERT statement, one or more of the columns specified
> > for the
> >
> > More...
> >
> > What am I doing wrong?
> >
> >
> >
> > Subject to Change Notice:
> >
> > WalzCraft reserves the right to improve designs, and to change
> > specifications without notice.
> >
> > Confidentiality Notice:
> >
> > This message and any attachments may contain confidential and
> > privileged information that is protected by law. The information
> > contained herein is transmitted for the sole use of the intended
> recipient(s) and should "only"
> > pertain to "WalzCraft" company matters. If you are not the intended
> > recipient or designated agent of the recipient of such information,
> > you are hereby notified that any use, dissemination, copying or
> > retention of this email or the information contained herein is
> > strictly prohibited and may subject you to penalties under federal
> > and/or state law. If you received this email in error, please notify
> > the sender immediately and permanently delete this email. Thank You
> >
> > WalzCraft PO Box 1748 La Crosse, WI, 54602-1748
> > http://www.walzcraft.com/<https://www.walzcraft.com/><http://www.walzc
> > raft.com/> Phone: 1-800-237-1326
> > --
> > This is the RPG programming on IBM i (RPG400-L) mailing list To post a
> > message email: [email protected] To subscribe, unsubscribe,
> > or change list options,
> > visit:
> > https://lists.midrange.com/mailman/listinfo/rpg400-l<https://lists.mid
> > range.com/mailman/listinfo/rpg400-l>
> > or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> > Before posting, please take a moment to review the archives at
> > https://archive.midrange.com/rpg400-l<
> https://archive.midrange.com/rpg400-l>.
> >
> > Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription
> > related questions.
> >
> >
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list To post a
> message email: [email protected] To subscribe, unsubscribe, or
> change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l<
> https://lists.midrange.com/mailman/listinfo/rpg400-l>
> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/rpg400-l<
> https://archive.midrange.com/rpg400-l>.
>
> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related
> questions.
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list To post a
> message email: [email protected] To subscribe, unsubscribe, or
> change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/rpg400-l.
>
> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related
> questions.
>
> CAUTION: This email originated from outside of the organization. Do not
> click links or open attachments unless you recognize the sender and know
> the content is safe.
>
>
>
> Subject to Change Notice:
>
> WalzCraft reserves the right to improve designs, and to change
> specifications without notice.
>
> Confidentiality Notice:
>
> This message and any attachments may contain confidential and privileged
> information that is protected by law. The information contained herein is
> transmitted for the sole use of the intended recipient(s) and should "only"
> pertain to "WalzCraft" company matters. If you are not the intended
> recipient or designated agent of the recipient of such information, you are
> hereby notified that any use, dissemination, copying or retention of this
> email or the information contained herein is strictly prohibited and may
> subject you to penalties under federal and/or state law. If you received
> this email in error, please notify the sender immediately and permanently
> delete this email. Thank You
>
> WalzCraft PO Box 1748 La Crosse, WI, 54602-1748
> www.walzcraft.com<https://www.walzcraft.com> Phone: 1-800-237-1326
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related
> questions.
>
>
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.
Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related questions.