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
> www.walzcraft.com<https://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://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.
>
>
-- 
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.