RE: ExternalRe: SQL Inserting into file based on records not in primary file
Jerry Forss <[email protected]> Sat, 21 Feb 2026 13:30:50 +0000
| Newsgroups | gmane.comp.lang.as400.rpg |
|---|---|
| Message-ID | <BL1PPF07C40B43A8F73835F6AA639F9FA46C669A@BL1PPF07C40B43A.namprd22.prod.outlook.com> |
Thank you Marco. We will be in Italy in a couple of months and VERY excited to get there. -----Original Message----- From: RPG400-L <rpg400-l-bounces-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> On Behalf Of Marco Facchinetti Sent: Friday, February 20, 2026 11:28 AM To: RPG programming on IBM i <[email protected]> Subject: Re: ExternalRe: SQL Inserting into file based on records not in primary file 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.wal > > zc > > 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.m > > id 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. 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.