Re: ExternalRe: SQL Inserting into file based on records not in primary file
Eric Wesson <[email protected]> Fri, 20 Feb 2026 16:57:54 +0000
| Newsgroups | gmane.comp.lang.as400.rpg |
|---|---|
| Message-ID | <DM6PR05MB6780EF027EBEFF3A42327ACDC368A@DM6PR05MB6780.namprd05.prod.outlook.com> |
Which sql statement is it throwing the error on? Are you sure the "purge" is the same as the source file. I might be missing it but I don't see being created in your source. You could create the file with no data first to guarantee it matches...of course it could be dynamic. create table Purgetable as( select * from Library.File) with no data ________________________________ From: RPG400-L <[email protected]> on behalf of Jerry Forss <[email protected]> Sent: Friday, February 20, 2026 10:49 AM To: RPG programming on IBM i <[email protected]> Subject: RE: ExternalRe: SQL Inserting into file based on records not in primary file This looks promising, like Marco's. I do need it to be dynamic as the file/library can vary. -----Original Message----- From: RPG400-L <[email protected]> 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 <[email protected]> on behalf of Marco Facchinetti <[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 > https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.walzcraft.com%2F&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984086827%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=175iD4iY%2Fo8w43a2hthOlASAjDzArZhlJfzfcMHLA8E%3D&reserved=0<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.walzcraft.com%2F&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984113826%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=xkrYZ8WnuItqXco79Q4mtMCmozVJtR5%2BTgCOMUeE9S0%3D&reserved=0><https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.walzc%2F&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984134747%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=XNsrGwHnS2N2H1caPqKB0RPyxkWKtZ%2BXyV7q%2BqaccCA%3D&reserved=0<http://www.walzcraft.com/> > 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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984155087%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=hQo4SW%2B8VK0sFB0tzbllP3LXRYH5%2F%2B54292lOo%2BFbfk%3D&reserved=0<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.mid%2F&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984175717%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=ajcjUUBg8ViBIoaIKUM06bH2m0GjnRj9BeL5BBbi48E%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l> > range.com/mailman/listinfo/rpg400-l> > or email: [email protected] > Before posting, please take a moment to review the archives at > https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984195582%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=sj%2FcwOqdB0M5CqEMZGssFkrOZRTV2JN1B07gXFuS4eE%3D&reserved=0<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984215774%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=BfXx7wNl8EZWJA%2BGZN9fpxFSUsGnDOVRfzQOrQ36ju0%3D&reserved=0><https://archive.midrange.com/rpg400-l>. > > Please contact [email protected] 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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984238167%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=m7jssup58r%2FH3PXZ%2FJcBy%2FImndV5MPcPgJqPstetbnk%3D&reserved=0<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984258841%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=WhnGrQN93Iuh%2B%2BPhx2WM9tvR%2FRVPSax%2BLSFk%2F%2FMB8MY%3D&reserved=0><https://lists.midrange.com/mailman/listinfo/rpg400-l> or email: [email protected] Before posting, please take a moment to review the archives at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984279433%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=MFnaKfHTr1lsHyTah5p5BgmR8d1fkmKb%2BD%2BXu%2BnGtQQ%3D&reserved=0<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984299878%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=MADOAMKwo3n9a6HyscGxwitEl87a%2FI9lrluxBSMhG4o%3D&reserved=0><https://archive.midrange.com/rpg400-l>. Please contact [email protected] 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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984320681%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=P5gyBl3rC6rNjVY7007GeOP9VzaFQO17xawdLC2HIKY%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l> or email: [email protected] Before posting, please take a moment to review the archives at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984341630%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=w3%2FKkAI2Nx9pNq3r%2FQmJXtaSp7S2kSxKZHpDyg6Q9Mw%3D&reserved=0<https://archive.midrange.com/rpg400-l>. Please contact [email protected] 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 https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.walzcraft.com%2F&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984364018%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=m7aBc6XzbAnt6dzjXuRwsR7w32VXQxOIm2JSdLbI73w%3D&reserved=0<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.walzcraft.com%2F&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984387205%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=7BoHyyT704GwFxBCilGfDlxWTsy0ipI8BT77OhJJ7%2B8%3D&reserved=0><http://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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984408470%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=mtl2MkLv7XrMrYh8OgjqACnE%2F2XdML9rFyvMHeSAZA0%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l> or email: [email protected] Before posting, please take a moment to review the archives at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7Cfcff09d8dcd64b59b72208de70a013de%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072029984428479%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=z%2F8324PkCnvkZIW3Og3RpF9SUvZql9ErBAi%2FFJ%2Fqgs4%3D&reserved=0<https://archive.midrange.com/rpg400-l>. Please contact [email protected] 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: [email protected] Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l. Please contact [email protected] for any subscription related questions.