Rpgle

[John192]

Hi,

I want to write an equivalent RPGLE Program to generate a physical file and logic wise it should have same records genereated in file3 after using file1 and file2 like below SQL query.

Any idea how can we write the equivalent RPGLE program for the same?


select * from file1 where fld1='ABCDEF' and fld2= (int(current date) - 19000000) and fld3 in (select fld7 from file2 where fld8='1') and fld4 in ('TYM','IOP')


where fld1,fld2,fld3,fld 4 are from file1 and fld7, fld8 are from file2.
Please Note:- fld2 is that specific numeric field having a numeric type and a length of 7 and which has a date field in it in CYY/MM/DD format ( as in the DDS of the file Edit word with slashes have been defined in it's definition part length 7 with packed data type ) in which we want to have all
the records for current system date.


So How can we write an equivalent RPGLE program to generate a physical file which has same data which is generated from above SQL query?



Thanks....

[John192]

Hi IBM i experts, could you please respond soon.


Thanks much..

[JonBoy]

This sounds suspiciously like a homework problem but ...

It depends on whether file1 is keyed on fld1 or not. If so then use a SETLL to position the file followed by READE until EOF. Then use standard compare operations to find the desired records. If no key on fld1 the read through the whole file including fld1 in the selection logic,

[B.Hauser]

Why to go back and convert a SELECT-Statement into native I/O (seems like homework as Jon said).
If you just want to read the rows returned by the SELECT-Statement you may use embedded SQL.
Here is an example how to embedd SQL in RPG : https://www.mysamplecode.com/2011/07...or-db2-in.html

[John192]

Ok, but evenif I need to write RPGLE program using *loval,setll and reade etc. then how can we write so?

I tried below but not sure how to proceed further?


Fy2 if e k disk
Fy1 if e k disk
c dou %eof(y2)
c read y2
c if not %eof(y2)
c if fld1= 'ABCDEF' and fld2= (int(curren

so many syntax errors while comleting these statements in RPGLE..


Could you please advise how can we proceed here?




Thanks..

[UserName10]

Something like this

FFile1 IP E K DISK
FFile2 IF E K DISK
FFile3 O E DISK

If fld1 = 'ABCDEF'
   and %Date(fld2 : *CYMD) = %Date
   and fld4 IN %LIST('TYM' : 'IOP');

   Setll (fld3 : '1') File2;     // presuming File2 key is fld7 and fld8
   If %Equal;
     Write File3;
   EndIf;

EndIf;

[John192]

When tried to write line no 5th and 6th then getting sysntax error in program please refer attached screenshot for the same and help to advise accordingly.



Thanks...

[UserName10]

Don't use fixed form calcs. Don't omit equal signs and parenthesis. Since you're using SEU disable syntax checking.

[John192]

how can we disable syntax checking?

[John192]

how can we disable syntax checking?

Also just noted these fld7,fld8 of file 2 are not key fields now what to do?

Also i don't have their DDS just objects of these files ara available and i just could find this information using DSPFFD.




Thanks much..

[John192]

Hi,

Using Shift + F1 Change sessions Defaults ,I put 'N' to syntax checking : when added/modified to 'N' but what about "these fld7,fld8 of file 2 are not defined as key fields in file2 now what to do?

Also I don't have their DDS just objects of these files are available and I just could find this information using DSPFFD."



Thanks ...

[John192]

Hi,

Could someone please help here...


Thanks...

[John192]

I tried updated code with putting colon : in %list and used respective record formats of files still this program is giving these attached compilation errors, could someone please assist .



Thanks...

[John192]

I tried updated code with putting colon : in %list and used respective record formats of files still this program is giving these attached compilation errors, could someone please assist .



Thanks...

just uploading missed updated RPGLE program for these compilation errors.


Thanks...

[UserName10]

Create a logical file on FILE2

A           R FILE2R                    PFILE(FILE2)
A           K FLD7
A           K FLD8

Use the same format name as in the physical file and all the fields will be included.