files, fields and records

[John192]

Hi,

How can we write a RPGLE program which would select unique records based on three fields let's say file A has multiple fields in it but we just to select it's three unique fields,fld1,fld2,fld3 and based on these three unique field values from file A we want to write records in File2 so how can we achieve the same using RPGLE program any program example for this please?



Thanks...

[UserName10]

manuals - https://www.ibm.com/docs/en/i/7.4?topic=languages-rpg

example code - https://www.ibm.com/docs/en/ssw_ibm_...7.pdf#page=379


Although back level I think the 5.4 manual does a better job explaining the basics. see chapter 7

https://web.archive.org/web/20170807070650/https://www.ibm.com/support/knowledgecenter/en/ssw_i5_54/books/c0918160.pdf

[John192]

Thanks, but it does not show any example where we can read unique records from a file based on specific fields in it and then write first file's data in second file.

[MFisher]

If the 3 fields are unique, then a composite key of the 3 fields can CHAIN to FILE_1
If the CHAIN succeeds, use the data to write a record to FILE_2

[John192]

But What if , I do not wish to make composite key in my file A and want to use it's fields as it is and based on it's specific 3 unique fields want to write unique records from File A to File B (or second file File 2)

?



Thanks...

[MFisher]

You want to select 1 record, based on unique combination of 3 fields, that are not key fields ?

If so, you could use SQL instead

SQL --- Make sure there's only 1 record
SET :RCD_COUNT = (select count(*) from FILE_1 WHERE unique values)

RPG:
IF RCD_COUNT = 1

SQL:
SET :FLD1 = (SELECT .................WHERE unique values) , :FLD2 = (SELECT .............. WHERE unique values)

SQL:
INSERT INTO FILE_2 VALUES(:FLD1 , :FLD2 , :FLD3 )

ENDIF

(Disclaimer: I am rusty in RPG, and not knowledgeable of all the recent enhancements)

[John192]

Thanks, I wanted this in RPGLE program without use of any SQL and it's basically 3 fields which should have independently unique records in themselves ( means it's not like that we are joining these 3 fields and making it a unique record) so for this how can we select such unique records from a file whose 3 different fields are unique but they are not the key fields also this file may have other fields as well in it but main thing is that records selection criteria from this file is unique records from these 3 fields ,if we are able to find it in this file then need to write these records in a second file along with these 3 fields all other field values from file 1 should also be written in second file.

So with in RPGLE program it self ( without any SQL) how can we do so?



Thanks...

[MFisher]

Why don't you want to use SQL ?

[John192]

because I wanted to handle all these conditions inside a RPGLE program only which has other operations happening inside that RPGLE program so instead of writing a new SQLRPGLE progrm i wanted to amend existing RPGLE program with this new functionality in it.



Thanks..

[UserName10]

Obviously a logical is needed on File B.

read File A
dow not %eof(File A)
  chain (File A Field 1 : File A Field 2 : File A Field 3) File B;
  if not %found(File B)
    write to File B
  endif
  read File A
enddo

If only certain values of the three fields in File A qualify to be written to File B then check for that condition:

read File A
dow not %eof(File A)
  if  file a fields meet certain conditions
    chain (File A Field 1 : File A Field 2 : File A Field 3) File B;
    if not %found(File B)
      write to File B
    endif
  endif
  read File A
enddo

But What if , I do not wish to make composite key in my file A and want to use it's fields as it is and based on it's specific 3 unique fields want to write unique records from File A to File B (or second file File 2)?

[MFisher]

because I wanted to handle all these conditions inside a RPGLE program only which has other operations happening inside that RPGLE program so instead of writing a new SQLRPGLE progrm i wanted to amend existing RPGLE program with this new functionality in it.



Thanks..

Add SQL to your existing program. Change TYPE from RPGLE to SQLRPGLE.

[B.Hauser]

It seems more and more that John192 want us to do his homework
... or he is started in a new company and told them he knows IBM i, (RPG/CL) programming and SQL and now finds out he has no idea what to do, but instead of asking his co-workers he tries to let us do his work.

[John192]

Thanks,but i am getting error(RNF0368


The file is not allowed with built-in function %FOUND.)

now on this line :-

if not %found(File B)

file A,B I created two logical files as wanted to test it with logical files. please refer below program and DDS of files used in below sample program to test it currently just for 2 fields.

read File A
**FREE
dcl-f lp6 usage(*input) KEYED rename(rec:rec1) PREFIX(A_);
dcl-f lp7 usage(*OUTPUT) KEYED rename(rec:rec2) PREFIX(B_);
read REC1 ;
dow not %eof(lp6) ;
chain (A_ID : A_DOB) REC2;
if not %found(LP7);
write REC2;
endif ;
read REC1 ;
enddo ;
EVAL *INLR = *ON;

*********
*************** Beginning of data ********************************
001.00 R REC PFILE(P6)
002.00 ID
003.00 DOB
004.00 NAME
005.00 K ID
006.00 K DOB

***********
SEU==> LP7
FMT PF .....A..........T.Name++++++RLen++TDpB......Functi ons+++++++++++++++++++++++++++ ...+... 9 ...+... 0
*************** Beginning of data ************************************************** *************************************
001.00 R REC 220126
002.00 ID 3P 0 220126
003.00 DOB L 220126
004.00 NAME 15 220126
005.00 K ID 220126
006.00 K DOB 220126
************************************************** ****************
SEU==> P6
FMT PF .....A..........T.Name++++++RLen++TDpB......Functi ons+++++++++++++++++++++++++++ ...+... 9 ...+... 0
*************** Beginning of data ************************************************** *************************************
0001.00 R REC 220126
0002.00 ID 3P 0 220126
0003.00 DOB L DATFMT(*DMY) 220126
0004.00 NAME 15A 220126
****************** End of data ************************************************** ****************************************

Thanks..

[JohnLewis]

I think the error RNF0368 is because the file LP7 is not defined for input - usage(*input).

I would expect you would also get error RNF5202 ("Operation not allowed for file specified as Factor 2") for the CHAIN operation for the same reason.

[John192]

Yes, but if LP7 is not declared as *output then how to write records in second file (file 2 or file b) based on specific fields from file1 or file A ?

also i had to create key fields for both of these files where as mainly i wanted unique records for 3 fields currently just trying to test it for 2 fields as shared example in my last post but got that error.

eventhough i created two key fields or both files as it is still getting this error.


Thanks..