Re: ExternalRe: SQL Inserting into file based on records not in primary file
Marco Facchinetti <marco.facchinetti-kthxv0ud/[email protected]> Sat, 21 Feb 2026 17:51:50 +0100
| Newsgroups | gmane.comp.lang.as400.rpg |
|---|---|
| Message-ID | <CAMsgu4rzViTZHw2aD2uScHsdrRMXGZEHywwcKKKmf+6aawEjoA@mail.gmail.com> |
Have a nice holiday. I'm a good restaurant consultant, if needed.:-) Best regards -- Marco Facchinetti Mr S.r.l. Tel. 035 962885 Cel. 393 9620498 Skype: facchinettimarco Il giorno sab 21 feb 2026 alle ore 14:31 Jerry Forss <[email protected]> ha scritto: > 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. > > -- 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.