Re: ExternalRe: SQL Inserting into file based on records not in primary file -- RPG400-L

To keep it dynamic, I think the simplest way is to go with the create table (like Marco's solution) as your going to get hung up on the compile time defined RecLayout.
________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Jerry Forss <JForss@xxxxxxxxxxxxx>
Sent: Friday, February 20, 2026 10:49 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
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 <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Eric Wesson
Sent: Friday, February 20, 2026 10:47 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxx> on behalf of Marco Facchinetti <marco.facchinetti@xxxxxxxxx>
Sent: Friday, February 20, 2026 10:41 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
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 <JForss@xxxxxxxxxxxxx> 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: RPG400-L@xxxxxxxxxxxxxxxxxx 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: RPG400-L-request@xxxxxxxxxxxxxxxxxx
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 support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.