AS/400 Code Samples - AS/400 - SQLSAMP1 - Populate PRMAIL with the contents of PYCSQ (a 1-Shot Program) Author: George Pearson Company: Green Springs Software, Inc. www.green-springs.com Purpose: A Simple example of an IBM SQL RPL-ILE program that selects records from 1 Infinium file, joined to another, and updates a field from the primary file with the contents of a field in the joined file. This was created as a "1-time" Quick and dirty programmer's update program. Components: SQLSAMP1 SQLRPGLE Program Parameters: *None Written: 2003 ¹*--------------------------------------------------------------------------------------------* ¹* G r e e n S p r i n g s S o f t w a r e , I n c Ashland, Oregon * ¹*--------------------------------------------------------------------------------------------* ¹* Object Name: SQLSAMP1 * ¹* Description: Populate PRMAIL with the contents of PYCSQ A 1-Shot Program * ¹* Narrative : A 1 time program to copy the contents of PYCSQ in file PYPMS to field * ¹* PRMAIL in file PRPMS. These files are part of Infinium HR/Payroll. * ¹* Creation : 03/13/2003 * ¹* Author : George Pearson, Gren Springs Software, Inc. * ¹*--------------------------------------------------------------------------------------------* ¹*--------------------------------------------------------------------------------------------* ¹* This simple example of an IBM SQL RPL-ILE program selects records from 1 Infinium file, * ¹* joined to another, and updates a field from the primary file with the contents of a field * ¹* in the joined file. This was created as a "1-time" Quick and dirty programmer's update * ¹* program. * ¹*--------------------------------------------------------------------------------------------* fPRLMS uf e k disk Employee Master * éFile Definitions * ¹---------------- d PRPMS e ds Extname(PRPMS) Employee Master d PYPMS e ds Extname(PYPMS) Payroll Master *ã******************************************************************************************** * ¹ Main Line Calcs *ã******************************************************************************************** c @key_PR Klist c Kfld prer c Kfld pren *ã******************************************************************************************** * ¹ Process the records *ã******************************************************************************************** * Open the Cursor c Exsr SR_OPENC * Fetch the first record c Exsr SR_FETCH * Loop until no more records. c Dow Sqlcod <> 100 c @key_pr Chain prrms c If %Found c Eval prmail = pycsq c Update prrms c Exsr SR_FETCH c EndIF c EndDO c Exsr SR_CLOSEC * End Job c Eval *InLR = *On *ã******************************************************************************************** * ¹ ~SR_OPENC - Open the SQL Cursor *ã******************************************************************************************** CSR SR_OPENC BegSR c/exec sql c+ Declare c1 Cursor for c+ Select prer, pren, prstat, pycsq c+ From HRDBFTWEA/PRPMS c+ Join HRDBFTWEA/PYPMS on pyer=prer and pren=pyen c+ where prpay = 'H' And prer='030' And c+ prstat not in('COBRA', 'GARN', 'TRF', 'TERM') c/end-exec c/exec sql c+ open c1 c/end-exec CSR EndSR *ã******************************************************************************************** * ¹ ~SR_FETCH - Fetch the Data *ã******************************************************************************************** CSR SR_FETCH BegSR c/exec sql c+ fetch c1 c+ into :prer, :pren, :prstat, :pycsq c/end-exec CSR EndSR *ã******************************************************************************************** * ¹ ~SR_CLOSEC - Close the SQL Cursor *ã******************************************************************************************** CSR SR_CLOSEC BegSR c/exec sql c+ close c1 c/end-exec CSR EndSR *ã********************** End Of Source *******************************************************