How a work file created during runtime can be read in SQLRPGLE program to check the record count

140 pts.
Tags:
AS400 - Embedded SQL
AS400 RPGLE
AS400-DB2/400
RPG
Workfile created using clle with no keys during run time in qtemp. Read a permanent file with its first key, using setll *loval and read in Dow loop. Check if the first key value is found in work file present in qtemp. The corresponding field in workfile is it's 4th field. How to check this or what is the SQL query to check the record resistance.

 
NicyChn  Jul 2, 2016   6:09 AM 

 more details of the code below:

File1
-----

Status  EmpDOJ  Location   EmpID  EMPName
------  -------  -------   -----   ------

enabled 100516  Tx         MARUN   ARUN M 
Disbled 100213  Ind        TPHILP  PHILP THOMAS


File 2 (Key field EMPID2)
------

EmpId2   EmpFirstName2  EmpLastName2
-----   ------------- ------------
PRYAN     RYAN          PHILIP
TPHILIP   PHILIP        THOMAS


CLLE PROGRAM
------------
PGM

An outfile -> QTEMP/file1 is created during runtime with above details.
CALL SQLRPGLEPROGRAM

ENDPGM


SQLRPGLE PROGRAM
---------------

Main
_____

Part1:For all empid in file2 if a matching record is found in file 2 or not iam writing a record to file3

exec sql
Decalre cursor#1 cursor 
  select empid from qtemp/file1 orderby empid asc;

exec sql
Open cursor#1 cursor

exec sql
fetch ... 

Dow SQLSTT = '00000'
   chain empid  file2
   if % found file2
     write record to file3/field1..
   else 
      write record to file3/field2..
   endif
 exec sql fetch next..
endddo

exec sql
close cursor#1

Part2: For all record in file2 if a matching is not found in file 1 , write a record to file4.field1

setll  * loval file2
Read file2

Dow not%eof file2

i have to match  empid2 with empid in file1. if matching record is not found, then write record to file4.field1
 ---->Please can you help what is the best option here


enddo


 Part 1 is tested and working fine. Part2 i tried sql,but not sure if issue is with query or steps,it is not working.
Can you please help.


Software/Hardware used:
Qtemp4
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • CharlieBrowne

    You can use JOIN or EXCEPTION JOIN to validate record existence.

    I must say it is difficult to give a great answer here because I do not completely understand the full scope of what you are trying to accomplish and do not have visibility to any of your code.

    62,385 pointsBadges:
    report
  • NicyChn
    Thank you for the reply. Below is more details of the code

    File1
    -----

    Status  EmpDOJ  Location   EmpID  EMPName
    ------  -------  -------   -----   ------

    enabled 100516  Tx         MARUN   ARUN M 
    Disbled 100213  Ind        TPHILP  PHILP THOMAS


    File 2 (Key field EMPID2)
    ------

    EmpId2   EmpFirstName2  EmpLastName2
    -----   ------------- ------------
    PRYAN     RYAN          PHILIP
    TPHILIP   PHILIP        THOMAS


    CLLE PROGRAM
    ------------
    PGM

    An outfile -> QTEMP/file1 is created during runtime with above details.
    CALL SQLRPGLEPROGRAM

    ENDPGM


    SQLRPGLE PROGRAM
    ---------------

    Main
    _____

    Part1:For all empid in file2 if a matching record is found in file 2 or not iam writing a record to file3

    exec sql
    Decalre cursor#1 cursor
      select empid from qtemp/file1 orderby empid asc;

    exec sql
    Open cursor#1 cursor

    exec sql
    fetch ... 

    Dow SQLSTT = '00000'
       chain empid  file2
       if % found file2
         write record to file3/field1..
       else
          write record to file3/field2..
       endif
     exec sql fetch next..
    endddo

    exec sql
    close cursor#1

    Part2: For all record in file2 if a matching is not found in file 1 , write a record to file4.field1

    setll  * loval file2
    Read file2

    Dow not%eof file2

    i have to match  empid2 with empid in file1. if matching record is not found, then write record to file4.field1
     ---->Please can you help what is the best option here


    enddo


     Part 1 is tested and working fine. Part2 i tried sql,but not sure if issue is with query or steps,it is not working.
    Can you please help.

      


    140 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: