Re: SQL Inserting into file based on records not in primary file

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  [email protected]<mailto:[email protected]>

w watrust.com<https://www.watrust.com/>
a P.O. Box 2127

   Spokane, WA 99210-2127


________________________________
From: RPG400-L <rpg400-l-bounces-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> on behalf of Jerry Forss <[email protected]>
Sent: Friday, February 20, 2026 08:32
To: [email protected] <[email protected]>
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%2FRitdW2e5G9
 H8teZudhGv7AWfEn6uyE7Shk%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://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-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
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-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org 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.
-- 
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.