Question on how to read physical file using RCVF

[Matt_99999]

Hi,

Is it possible read the result of each column in the physical file using RCVF?

 START:      PGM

             DCL        VAR(&PTYPE) TYPE(*CHAR) LEN(130)
             DCLF       FILE(QTEMP/TSTTMP)


             RUNSQL     SQL('DROP TABLE QTEMP/TSTTMP') COMMIT(*NONE)
             MONMSG     MSGID(SQL0000)


             RUNSQL     SQL('CREATE TABLE QTEMP/TSTTMP AS (SELECT DISTINCT IMORIG, IMREF, LELOPR, +
                          ''test@test.com'' AS EMAIL FROM LIBTEST/TABLE WHERE TRIM(LESTAT) = +
                          '''' AND OPDORD = ''250522'' AND LELOPR = LECTRL LIMIT 1) WITH DATA') +
                          COMMIT(*NONE) NAMING(*SQL)

             MONMSG     MSGID(SQL0000)

 READ:       RCVF
             MONMSG     MSGID(CPF0864) EXEC(GOTO CMDLBL(END1))
             CHGVAR     VAR(&PTYPE) VALUE('Type: ' || &IMORIG || ' Reference: ' || &IMREF || ' Email: +
                          ' || &EMAIL )

 END1:       SNDUSRMSG  MSG('THE END OF FILE IS REACHED')

 EXIT:       ENDPGM​

[B.Hauser]

Yes, why not?
... but if you want to use *SQL Naming Conventions, you have to separate schema and object with a period and not a slash.

But why creating a file? Creating a view would be better.

The following source code works for me:

             DCLF       FILE(UmsTotV)

             RUNSQL     SQL('Drop View QTEMP/UmsTotV') COMMIT(*NONE)
             MONMSG     MSGID(SQL0000)

             RUNSQL     SQL('Create View QTEMP/UmsTotV                         +
                             as (Select Cast(Coalesce(Sum(Amount), 0)          +
                                             as Dec(11, 2)) GesUmsatz          +
                                      From Sales                               +
                                      Where     Year(SalesDate) = '            +
                                                     *CAT &ParJahrA *BCAT      +
                                          ' and CustNo =   ''' *CAT &ParKndNr  +
                                                               *TCAT ''' )     +
                             RcdFmt UmsTotVF')                                 +
                        COMMIT(*NONE)

             RCVF       RCDFMT(UmsTotVF)
             MONMSG     MSGID(CPF0864) EXEC(DO)
             SNDPGMMSG  MSG('Kunde:'    *BCAT &ParKndNr *TCAT            +
                            ' - Umsatz' *BCAT &ParJahrA *CAT  ': 0,00 ') +
                        ToPgmQ(*EXT)
             GOTO       CMDLBL(ENDE)
             ENDDO

 ENDE:       ENDPGM                                                                                ​

[Matt_99999]

Reason for creating a file is to retrieve single row using the clause limit 1.

[Matt_99999]

Hi,

I tried compiling the the CL program but getting the following errors:

* CPD0869 40 Record format 'EGHTMPF ' not found in DCLF command.
* CPD0727 40 Variable &IMORIG is referred to but not declared.
* CPD0727 40 Variable &IMREF is referred to but not declared.
* CPD0727 40 Variable &EMAIL is referred to but not declared.

    103- /**                                                                 **/                          06/01/20
    104-                                                                                                  06/01/20
    105- /**                                                                 **/                          06/01/20
    106- /** Before compile run the below line                               **/                          13/12/22
    107- /** CRTPF FILE(QTEMP/EGHTMP) RCDLEN(38) OPTION(*NOSRC *NOLIST)      **/                          15/12/22
    108-                                                                                                  06/01/20
    109-  START:      PGM                                                                                 04/12/22
    110-                                                                                                  10/12/22
    111-                                                                                                  15/12/22
    112-              DCL        VAR(&PTYPE) TYPE(*CHAR) LEN(130)                                         13/12/22
    115-                                                                                                  15/12/22
    116-              DCLF       FILE(EGHTMP)                                                             15/12/22

         QUALIFIED FILE NAME - QTEMP/EGHTMP

            RECORD FORMAT NAME - EGHTMP

              CL VARIABLE               TYPE     LENGTH     PRECISION     TEXT
               &EGHTMP                  *CHAR      38

    117-                                                                                                  14/12/22
    118-              RUNSQL     SQL('DROP VIEW QTEMP/EGHTMP') COMMIT(*NONE)                              15/12/22
    119-              MONMSG     MSGID(SQL0000)                                                           14/12/22
    120-                                                                                                  10/12/22
    121-                                                                                                  10/12/22
    122-              RUNSQL     SQL('CREATE VIEW QTEMP/EGHTMP AS (SELECT X.IMORIG, X.IMREF, X.EMAIL +    15/12/22
    123                            FROM(SELECT IMORIG, IMREF, LELOPR, ''testabcd@testabde12'' AS EMAIL, + 15/12/22
    124                            ROW_NUMBER() OVER () ROWNUM FROM TESTLIB/FDBIMO WHERE TRIM(LESTAT) = ' 15/12/22
         ''' +

5770SS1                                 Control Language         TSTPGMXX/MT_EMAIL              14/12/22 10:13:17         Page    2

                                      Control Language Source

 SEQNBR  *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+.  DATE

    125                            AND OPDORD = ''250522'' AND LELOPR = LECTRL) X WHERE X.ROWNUM = 1) RCD 15/12/22
         FMT +
    126                            EGHTMPF') COMMIT(*NONE)                                                15/12/22
    127-                                                                                                  14/12/22
    134-                                                                                                  14/12/22
    135-              MONMSG     MSGID(SQL0000)                                                           10/12/22
    136-                                                                                                  10/12/22
    137-  READ:       RCVF       RCDFMT(EGHTMPF)                                                          15/12/22
* CPD0869 40  Record format 'EGHTMPF   ' not found in DCLF command.

    138-              MONMSG     MSGID(CPF0864) EXEC(DO)                                                  15/12/22
    141-                 SNDPGMMSG  MSG('Type: ' || &IMORIG || ' Reference: ' || &IMREF || ' Email: ' | 15/12/22
         | &EMAIL)
* CPD0727 40  Variable &IMORIG is referred to but not declared.

* CPD0727 40  Variable &IMREF is referred to but not declared.

* CPD0727 40  Variable &EMAIL is referred to but not declared.

    143-                                                                                                  15/12/22
    144-              GOTO       CMDLBL(EXIT)                                                             15/12/22
    145-              ENDDO                                                                               15/12/22
    200-                                                                                                  06/01/20
    201-  EXIT:       ENDPGM                                                                              06/01/20

                               * * * * *   E N D   O F   S O U R C E   * * * * *​

[MFisher]

Double check the PF file name and RCDFMT name

[Brian Rusch]

Whether you use a physical file or a view, in order for the CL program to know about the columns in the file/view, it has to be there when you compile the CL program. It doesn't work to create a generic file with CRTPF QTEMP/EGHTMP RCDLEN(38) because that will create a file with one field in it named the same as the file name (also the record format name will be the same as the file name). The order of the steps are to run the SQL to create the table/view and then compile and run the CL program.

[Brian Rusch]

The file/view has to be created before you compile the CL program one time only. Once the program is compiled, the file/view does not have to exist before running the program since it's created inside the program.

[Peder Udesen]

Just a stupid question:
Are you compiling in batch?

If so then it can't of course find the file if you have created it interactively in QTEMP.
I have seen this happen before.

[Matt_99999]

Thanks,

I changed the query to create a physical file instead and recompiled it and run it interactively.
But it was unable to display sndpgmsmg.

Below is the result:

CALL TSTPGMHB/MT_EMAIL                                              
   123 - RUNSQL SQL('DROP TABLE QTEMP/EGHTMP') COMMIT(*NONE)        
   127 - RUNSQL SQL('CREATE TABLE QTEMP/EGHTMP AS (SELECT DISTINCT  
  IMORIG, IMREF, LELOPR, CAST(''test@testabc.com'' AS CHAR(20)) AS
  EMAIL FROM TSTLIB/FDBIMO WHERE TRIM(LESTAT) = '''' AND OPDORD =  
  ''250522'' AND LELOPR = LECTRL LIMIT 1) WITH DATA RCDFMT EGHTMPF')
  COMMIT(*NONE)                                                      
   137 - RCVF DEV(*FILE) RCDFMT(EGHTMPF) WAIT(*YES) OPNID(*NONE)    
  /* File name is QTEMP/EGHTMP. */                                  
       - RETURN        /* RETURN due to end of CL program */        ​

[Matt_99999]

I had commented out the following line:

MONMSG MSGID(CPF0864) EXEC(DO)
ENDO

And the result got displayed.

Thank you all,
I appreciate the help