Looks like you are missing a closing parentheses:
Try this line with the closing parentheses added at the end.
IncludeRecords = 'alcucd < 0 And alcucd Not In (Select s2cucd From amfliba/mbs2rep');
Coy Krill
EIS Team Lead, AVP
Enterprise Information Systems
[Logo]<
https://www.watrust.com/>
o 509.358.8148
c 509.202.2769
t 800.788.4578
e ckrill@xxxxxxxxxxx<mailto:ckrill@xxxxxxxxxxx>
w watrust.com<
https://www.watrust.com/>
a P.O. Box 2127
Spokane, WA 99210-2127
________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Jerry Forss <JForss@xxxxxxxxxxxxx>
Sent: Friday, February 20, 2026 08:32
To: rpg400-l@xxxxxxxxxxxxxxxxxx <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL Inserting into file based on records not in primary file
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://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.walzcraft.com%2F&data=05%7C02%7Cckrill%40watrust.com%7Cb9471e2359ad4aa6afb808de709da773%7C9c064d396f1540cc816ad14c578e7917%7C0%7C0%7C639072019645939778%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=KAXtkrsRfP2TvCOIWEkZHSTAiU%2BmjO%2FjiQm1t07p5T4%3D&reserved=0<
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.walzcraft.com%2F&data=05%7C02%7Cckrill%40watrust.com%7Cb9471e2359ad4aa6afb808de709da773%7C9c064d396f1540cc816ad14c578e7917%7C0%7C0%7C639072019645971246%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=J03BT7%2FS%2FRitdW2e5G9H8teZudhGv7AWfEn6uyE7Shk%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: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7Cckrill%40watrust.com%7Cb9471e2359ad4aa6afb808de709da773%7C9c064d396f1540cc816ad14c578e7917%7C0%7C0%7C639072019645998912%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=AZLlAotLXthTCsuVQr29XzcHC6c42OT6IfIzqDIPSXE%3D&reserved=0<
https://lists.midrange.com/mailman/listinfo/rpg400-l>
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7Cckrill%40watrust.com%7Cb9471e2359ad4aa6afb808de709da773%7C9c064d396f1540cc816ad14c578e7917%7C0%7C0%7C639072019646022207%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=0EQwgVRpEIDuh7nvk%2FSaK9KIDMJYp0Qa%2BLIbPGz%2F61Q%3D&reserved=0<
https://archive.midrange.com/rpg400-l>.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
This electronic mail message and any attachments may contain confidential or privileged information and is intended for use solely by the above-referenced recipient. Any review, copying, printing, disclosure, distribution, or other use by any other person or entity is strictly prohibited under applicable law. If you are not the named recipient, or believe you have received this message in error, please immediately notify the sender by replying to this message and delete the copy you received.
midrange.com
