ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Select Count(*) in CL ?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Select Count(*) in CL ?

    I would like to get a record count of a file in a CL Program. If "Processed 'N' = 0, I want to skip processing. If Processed 'N' > 0 then continue processing.
    I am a bit familiar with RUNSQL to execute SQL statements, but not sure how to get the result into the CL to check the result.
    Any suggestions ?

    Edit: I could create a logical file, where Processed *NE 'N' then use RCVF and MONMSG CPF0864. Would that work if 0 records in the LF ?
    Last edited by MFisher; January 13, 2021, 12:04 PM.

  • #2
    Hi. Try with RTVMBRD. NBRCURRCD value return de number of records. Sample: RTVMBRD FILE(MYLIB/TSTPF01) MBR(*FIRST) NBRCURRCD(&NBRCURRCD)

    Comment


    • #3
      There will be records, but I want a count where PROCESSED = 'N'.
      I don't think I can be selective with RTVMBRD
      Last edited by MFisher; January 13, 2021, 01:05 PM.

      Comment


      • #4
        What is PROCESSED = 'N'. ?

        Comment


        • #5
          I want to find out if there are any records in a physical file, where the field "Processed" = 'N'
          If there are 0 records, I want to skip to the end of the CL program.

          Comment


          • #6
            The problem with RUNSQL is, you cannot use it in composition with a SELECT statement, because there is no way to receive the result.
            As work around, you can create a temporary SQL view (based on your SELECT Statement) in your CL-Programm and then read the view with SQL.
            Something linke this:

            Code:
                         DCLF       FILE(TempView)
            
                         RUNSQL     SQL('Drop View QTEMP/TempView') COMMIT(*NONE)
                         MONMSG     MSGID(SQL0000)
            
                         RUNSQL     SQL('Create View QTEMP/TempView  +
                                         as (Select Count(*) +
                                                  From YourTable  +
                                                  Where     Processed = ''N'' )  +
                                         RcdFmt TempViewF')  +
                                    COMMIT(*NONE)
            
                         RCVF       RCDFMT(TempView)
                         MONMSG     MSGID(CPF0864) EXEC(DO)
            Birgitta
            Last edited by B.Hauser; January 14, 2021, 01:17 AM.

            Comment


            • #7
              The simplest must be to create a logical file selecting the records where Processed = 'N'.
              Then retrieve the number of records in the logical file using RTVMBRD that INO suggested.

              In the help for RTVMBRD you can find:
              If the member is a keyed logical member, the number of index entries
              is returned. For nonkeyed logical members, the number of nondeleted
              records in the based-on physical file member is returned.


              Comment

              Working...
              X