AS/400 Query – do not print report with ‘no records found’

25 pts.
Tags:
AS/400
AS/400 Query
I am running a query interactively but I don't know how to do it with CL and SQL.

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 6  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • ToddN2000
    How are you running the query? interactive or in a batch CL?  If batch process try checking your files with SQL to see if data exists before running the query to produce the report.
    8,620 pointsBadges:
    report
  • bnobles1
    I am running it interactively. I do not know how to do it with CL and SQL.
    25 pointsBadges:
    report
  • MDratwa
    There are multiple ways to check to see if there are records.  You can create a SQL to create a work file and check the number of records.  Run OPNQRYF with the matching selection and check the number of records.  I am sure more options can get the number of records.  I suggest that this is run in batch to save the interactive processing for true inactive jobs.
    785 pointsBadges:
    report
  • MDratwa
    In SQL

    select count(*) from library/filename
          where fielda = 'xxx'  (fielda equal value 'xx' (alphanumeric) or 99 (numeric))

              or

    select count(*) from library/filename
          where fielda <> 'xxx' (fielda not equal 'xx' or 99)

    can use "and' and 'or' for multiple selections (like query) and can use "insert library/filename" in front of select

    use "RTVMBRD" in CL and use the "NBRCURRCD" parm to get the number of records in work file

    can use query to create a work file and use the RTVMBRD to get the number of records and rerun the query again using the print option is number of records is non-zero.
                   
    785 pointsBadges:
    report
  • bnobles1

    Thanks for all of the posts...


    I have created a CL program to run the queries.  One query, as suggested, creates a temporary file to be checked for the existence of records.  The other query will print the report (if records exist in the temporary file).

    I guess I have an issue with the CL programming.  It prints the report even if the temporary file has no records.  Here is the code I am using:

    PGM

         DCLF FILE(QAFDMBR)

         RUNQRY QRY(MYLIB/FILEQRY)

         DSPFD FILE(PRODLIB/TEMPF) TYPE(*MBR) OUTPUT(*OUTFILE) +

                                                    FILEATR(*ALL) OUTFILE(QTEMP/QAFDMBR)

         OVRDBF FILE(QAFDMBR) TOFILE(QTEMP/QAFDMBR) POSITION(*START)

         RCVF

         IF COND(&MBNRCD *GT 0)
              RUNQRY QRY(MYLIB/REPORTQRY)

    ENDPGM



    25 pointsBadges:
    report
  • philpl1jb

    Your If structure won't work ..If then ... as below.

    The way it's coded if it's true do noting.

    Then in all cases run the query.

    I think you will find that the RVMBRD is more effective.

    PGM
         DCL &RCDCNT TYPE(*DEC) LEN(10 0)
         RUNQRY QRY(MYLIB/FILEQRY)
         RTVMBRD FILE(PRODLIB/TEMPF) NBRCURRCD(&RCDCNT)
         IF COND(&RCDCNT *GT 0) +
              THEN(RUNQRY QRY(MYLIB/REPORTQRY))
    
    ENDPGM
    
    

     

     

    49,590 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following