Wednesday, July 24, 2013

Passing parms to a Query

Query/400 has always provides a quick and easy way to generate a report or create an output file. I have found that once they are built they never go away, as a programmer never has time, with all the demands on their time, to replace it with a RPG program.

If a Query joins several large files it can become a resource hog, taking away CPU power from normal daily interactive processing. The solution has always been to run Queries in batch.

Most people find that if they need to change the Query’s run parameters they use the Record Select parameter, RCDSLT(*YES), in the Run Query command, RUNQRY.

  RUNQRY QRY(QUERY_NAME) RCDSLT(*YES)

Of course this cannot be run in batch. Therefore, this Query, with its large joins, runs interactively, and the other users complain that the system is running slow.

But there is another way using the Start Query Management Query, STRQMQRY, command.

In this example we have a Query, TEST, which lists all of the orders of a certain status within a date range. The Status field is a one character field called STS. The Order Date is an eight long packed numeric field, called ORDDTE.

The current selection criteria in the Query are:

                                Select Records

Type comparisons, press Enter.  Specify OR to start each new group.
  Tests:  EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...

AND/OR  Field             Test   Value (Field, Number, 'Characters'
        STS              EQ     'S'                          
        ORDDTE           RANGE  20130101 20131231            
                                                             

Why does he do that?   There is no AND in the second line of selection criteria as Query assumes that if there is no value it is AND.

First, we need to change the values in the selection criteria to be variables:

                                 Select Records 

Type comparisons, press Enter.  Specify OR to start each new group.
   Tests:  EQ, NE, LE, GE, LT, GT, RANGE, LIST, LIKE, IS, ISNOT...  

 AND/OR  Field             Test   Value (Field, Number, 'Characters'
         STS              EQ     :STS                         
         ORDDTE           RANGE  :FRMDTE :TODTE               
                                                              

The Status of ‘S’ has been replaced by the variable :STS, yes it must start with a colon, and the Order Date range by variables :FRMDTE and :TODTE.

When Enter is pressed we are presented with the ‘Specify Dependent Value Qualifiers’ screen:

                     Specify Dependent Value Qualifiers

Type choices, press Enter.

  Qualifier type . . . .   1              1=Query, 2=File
                                                                     
  Query or file  . . . .                Name, F4 for list of files
    Library  . . . . . .     QGPL        Name, *LIBL, F4 for list

  For choice 2=File:
    File member  . . . .                Name, *FIRST, F4 for list

I have to admit I do not know what the functionality of the screen is. All I know to do it to enter something in the ‘Query or file’ field, for example: TEST, and press Enter.

You cannot press F5 to run the Query when editing it as you will receive the following message: ‘Dependent value used, cannot show report.’

Now we need to do some CL programming. I am not going to give the code on how to create a screen that allows the requester to enter the Status and From and To dates. All I am going to give is the program that is submitted to batch.

The program receives three parameters, see line 1 below, from the program that submitted it to batch:

  • &STS – Status code – 1 character.
  • &FRM – From date – 8 characters.
  • &TO – To date – 8 character.
01          PGM        PARM(&STS &FRM &TO)
02
03          DCL        VAR(&STS) TYPE(*CHAR) LEN(1) 
04          DCL        VAR(&FRM) TYPE(*CHAR) LEN(8) 
05          DCL        VAR(&TO)  TYPE(*CHAR) LEN(8) 
06
07          DCL        VAR(&STS2) TYPE(*CHAR) LEN(10) 
08
09          CHGVAR     VAR(&STS2) VALUE('''' || &STS || '''')
10
11          STRQMQRY   QMQRY(TEST) OUTPUT(*PRINT) QMFORM(*QMQRY) +
12                         ALWQRYDFN(*YES) +
13                         SETVAR((STS &STS2) +
14                                (FRMDTE &FRM) +
15                                (TODTE &TO))
16
17          ENDPGM

Line 7 has the field &STS2 defined as 10 characters. This field has to be large enough to contain the value of &STS and the apostrophes needed to make it appear character to the Query.

Line 9 adds the apostrophes either side of &STS, so that it will appear as ‘x’ to the Query.
Why does he do that?   The double pipe, ||, does the same as the *CAT expression. I just think it is easier to enter and more pleasing on the eye.

Line 11 is the start of the STRQMQRY command.

Line 12 has the ALWQRYDFN(*YES) parameter which tells the command to use the Query TEST.

Line 13, 14, and 15 is where we tell the Query what values to use. The variable name we defined in the Query appears first followed by the CL variable name.

Field &STS2 has leading and trailing apostrophes to denote that it is a character/alphanumeric field, which matches the definition of the file field STS.

Fields &FRM and &TO have no apostrophes, therefore, the Query assumes that they are decimal/numeric, which matches the definition of the file field ORDDTE.

If we wanted to produce an output file, in this example a file called TESTFILE in the library QTEMP, we would change the STRQMQRY command. The OUTPUT parameter would be *OUTFILE and we would need to give the OUTFILE parameter:

            STRQMQRY   QMQRY(TEST) OUTPUT(*OUTFILE) QMFORM(*QMQRY) +
                           OUTFILE(QTEMP/TESTFILE) +
                           ALWQRYDFN(*YES) +                           
                           SETVAR((STS &STS2) +
                                  (FRMDTE &FRM) +
                                  (TODTE &TO))

Now you have a simple way of always running Queries in batch, even those that need parameters to determine what data to extract.

 

This article was written for IBM i 7.1, and it should work with earlier releases too.

24 comments:

  1. It does not work for exception Join and some of the keywords also doesn't work.

    It has a performance issue as well. For large files it takes lot of time to process it.

    Any suggestions for improvement?

    ReplyDelete
    Replies
    1. I agree with both your points. There are things you cannot do in Query/400 that you can in a Query Management query. And running a Query/400 that joins large files together will take a lot of time.

      My suggestion is to do what I mentioned the first paragraph: write a RPGLE/RPG IV program to replace the Query/400.

      With the demands I have upon me I do not have the time to write RPGLE programs to replace the over 400 Query/400 that are used. I have taken all of these Query/400 and called then using the STRQMQRY command, using screens for users to input the parameters required. All have been tested and approved by the users as producing the same output as the Query/400 did.

      Delete
  2. I tried today, and.... yes may be different way tu make QMqry. I will try with more complex and I will report. Thank for suggestion.
    Roberto

    ReplyDelete
  3. Thanks very much I have been searching all over the net for a simple explanation on how the setup works for STRQMQRY and SETVAR and you have provided me with an explanation I can use. Thanks again.

    ReplyDelete
  4. Thanks a lot !! Now Is working perfectly what I needed

    ReplyDelete

  5. when creating the query how to parameter the library of the data source

    ReplyDelete
    Replies
    1. You do that in the RUNQRY command:
      RUNQRY QRY(QRYNAME) QRYFILE((LIBRARY/FILE_NAME))

      It is better to have:
      RUNQRY QRY(QRYNAME) QRYFILE((*LIBL/FILE_NAME))

      Delete
  6. How can I override the input file with a variable like &user?

    ReplyDelete
  7. What would be nice is with query/400 your selection criteria page would print on the Specify Cover Page (if you select Y to print it). We have some CL with querys that use Record Selection =Yes. We enter a date range and the query works fine but unless you write the date range on the report you won't have a clue of what date range it was for.

    ReplyDelete
  8. I have a query that is using 'like' for the test that I would like to converted to QMQ but, I cannot use a Value of :XXX with the like test. Why would this be. We are on version 7.2. thanks.

    ReplyDelete
  9. Hi, i must performe a query into a programm rpg, when i performe the program rpg i should get a form of data entry, where i can insert the value for get the result of the query, how can i do?

    ReplyDelete
  10. I think you are expecting too much from Query, it is just a simple tool.

    You ought to look into converting the Query into a SQL statement you have in a SQLRPGLE, that way you have better control of what you want and how you want the data.

    A good place to start is with these posts...
    Getting the SQL statement out of Query/400
    Reading a SQL table in RPG
    Reading a SQL table in SQL
    SQL blocking fetches, getting more than one row at a time

    ReplyDelete
  11. I am using your example to pass two dates to a query and produce an output file. When I run the CL, it returns a result set, but when I run the query independently without the dependent values but as date values, I get a different number of records. Any idea of what would cause the difference, with everything else being the same?

    ReplyDelete
    Replies
    1. The first thing that springs to mind is library list. Are you 100% sure that the two Queries are using the same file in the same library?

      Try using the QRYFILE parm in the RUNQRY command to be the name of the file and *LIBL

      Delete
  12. Hi
    Simon thank you for the good example (us usual )
    I am interesting if there is possible workaround on using parameters for LIKE / NLIKE comparison operator
    ( query/400 does not permit using variables with them and
    my shop unfortunately has not interactive SQL :-( in list of installed license programs )

    ReplyDelete
    Replies
    1. It has been many, many years since I last used an IBM i without interactive SQL being loaded. If my memory servers me right you can still build SQL statements in source members. You can wither compile them using CRTQMQRY or just run them using RUNSQLSTM.

      I would try placing your SQL statement in source member with the parameter for the LIKE etc. Compile it using CRTQMQRY. Try it. It has been a long time since I did this, but it just might work.

      Delete
  13. Can you specify a variable in the 'Define result fields' ? Something like SUBSTR(FLD1 , 1 , :LEN) ........... I want to substring FLD1 but the length is not fixed , its input by the user.... i tried but its not working !!

    ReplyDelete
    Replies
    1. If it did not work then you need to come up with another approach.

      I would extract the SQL statement from the Query.

      Then insert the SQL into a RPG or CL program. You should be able to do what you want then.

      Delete
  14. Thanks, I got this to work passing parameters to my *QRYDFN query, but lost my column formatting (defined in the WRKQRY column definitions). Is there a way to access that from the STRQMQRY call? Thanks.

    ReplyDelete
  15. Hi Simon, I have an issue we're it says message with ID QWM1913 , as the variable is declared and the values are passes to it still it throws as error could you share some thoughts .I also checked variables are declared in CAPS

    ReplyDelete
  16. Hi Simon, each time I'm running QMQRY with SETVAR system is throwing error "Column or global variable XXXX not found. RUN QUERY command failed with SQLCODE -206" any idea ? what could have gone wrong?
    Note: XXXX is partial data of my passing variable.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.