Re: SQL Inserting into file based on records not in primary file
Eric Wesson <[email protected]> Fri, 20 Feb 2026 16:46:30 +0000
| Newsgroups | gmane.comp.lang.as400.rpg |
|---|---|
| Message-ID | <DM6PR05MB67807074EB98F67BD77C370EC368A@DM6PR05MB6780.namprd05.prod.outlook.com> |
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%7C053a35a3ad3e45797da408de709ee5a2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072024948173273%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=2X11Pi4CIpSRoEEDWyvUeolPtKsyxJpGyz1qTWH9wns%3D&reserved=0<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.walzcraft.com%2F&data=05%7C02%7C%7C053a35a3ad3e45797da408de709ee5a2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072024948213593%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=%2F7p%2FQ9hDnPjIq3J4VxN0I9lHp5Y%2FWOpODTkLap36%2FSk%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%7C053a35a3ad3e45797da408de709ee5a2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072024948246459%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=rAFFIRWZ0N%2FVOR1ecoikRrM5t47jFZmRuY3F6C87DF0%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%7C053a35a3ad3e45797da408de709ee5a2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072024948278882%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=zQPfIRmg6vAfzL1QoZc61qhOuR2p%2BuMvfq0aEp%2FKEKo%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%7C053a35a3ad3e45797da408de709ee5a2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072024948307294%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=vax8tyLjZ8CA6XvalVbwJmwm6HDIs3fZtAzziNIN6GI%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%7C053a35a3ad3e45797da408de709ee5a2%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639072024948331915%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=FrfTBsO6SThxL0jlRn%2BoSs3cPosjz8VnUovhy1ZLoUc%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.