ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Changing query date daily

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

  • Changing query date daily

    Hello,

    Is is possible to create a CL that automatically change a query date every day? I'm planning to have the Call program run on schedule by putting it in the WRKJOBSCDE.
    So far all i managed to obtain is the select command of the query by using the RTVQMQRY command and here is what i got:

    Code:
    000100212901H QM4 05 Q 01 E V W E R 01 03 21/29/01 13:50
    000200212901V 1001 050 QUERY RESULT FOR TODAY
    000300212901V 5001 004 *HEX
    000400212901SELECT
    000500212901 ALL T01.TBRN, T01.TCDNO, CXNAMK AS CIF, T01.TPLAN#, T01.TNAM,
    000600212901 (T01.TBITRT), T01.TISSD, T01.TCBL
    000700212901 FROM VISIONA/TBAL T01 INNER JOIN
    000800212901 VISIONA/CXREF T02
    000900212901 ON TCDNO = CXNOAC
    001000212901 WHERE TPLAN# IN (231, 241, 251, 261)
    001100212901 AND TISSD = 20210129
    001200212901 ORDER BY 006 ASC, 001 ASC
    the only variable that i wish to change is the 20210129. i usually manually changed it from the WRKQRY select record but is it possible to change it daily using CL ? What other information do i need to collect ?


    Thank you

  • #2
    Do you want the current date? Can you use SQL via RUNSQLSTMT or STRQM?

    Comment


    • #3
      We don't have WRKQRY but isn't there a way in that to get the current date? Something like 'CURRENT DATE' that you can use in sql. Another option would be to use a view for the entire statement using CURRENT DATE instead of the hard coded value and then use the view in the query.

      Comment


      • #4
        Query can retrieve the current date, and you can adjust the retrieved date if you need to. Define a result field with the expression current(date) and adjust as needed, e.g. to get the previous day. You can use that result field for record selection.

        Comment


        • #5
          Originally posted by jtaylor___ View Post
          Do you want the current date? Can you use SQL via RUNSQLSTMT or STRQM?
          Yes both commands are available but is it possible to run it using CL ? only to change this part
          AND TISSD = 20210129

          Comment


          • #6
            Originally posted by Scott M View Post
            We don't have WRKQRY but isn't there a way in that to get the current date? Something like 'CURRENT DATE' that you can use in sql. Another option would be to use a view for the entire statement using CURRENT DATE instead of the hard coded value and then use the view in the query.
            Are you refering to using the STRSQL to retrieve the date and then retrieve the date using current date ? Is it possible to use STRSQL command in creating CL ?

            Comment


            • #7
              Originally posted by TedHolt View Post
              Query can retrieve the current date, and you can adjust the retrieved date if you need to. Define a result field with the expression current(date) and adjust as needed, e.g. to get the previous day. You can use that result field for record selection.
              The thing that i already adjust is that i retrieved the date from QDATE changed the format to YYYYMMDD and put it in as a *DTAARA which will later be used to replace the date variable daily, what i'm still trying to figure out is how do i change date value in the TISSD EQ 20210129 field.

              Comment


              • #8
                Originally posted by Lemon Grande View Post

                Yes both commands are available but is it possible to run it using CL ? only to change this part
                AND TISSD = 20210129
                Do you want the current date?

                Comment


                • #9
                  Originally posted by jtaylor___ View Post

                  Do you want the current date?
                  Yes the current date, so far this is what i managed to do in order to get the current date from system and converted it to be the same format (YYYYMMDD)

                  Code:
                  PGM
                  
                  DCL VAR(&DATE) TYPE(*CHAR) LEN(10)
                  DCL VAR(&DATEFMT) TYPE(*CHAR) LEN(6)
                  CHKOBJ OBJ(JOHN/DATE) OBJTYPE(*DTAARA)
                  RTVSYSVAL SYSVAL(QDATE) RTNVAR(&DATEFMT)
                  CVTDAT DATE(&DATEFMT) TOVAR(&DATE) TOFMT(*YYMD) +
                  TOSEP(*NONE)
                  CHGDTAARA DTAARA(JOHN/DATE (1 12)) VALUE(&DATE)
                  
                  ENDPGM
                  I've yet to find the way to change the query field.

                  Comment


                  • #10
                    I probably don't understand what you're trying to accomplish, Lemon.

                    It seems to me that you have a query that you want to select records for the current date. You've used RTVQMQRY to get the SQL that retrieves the data so that you'll have a way to change the date used for record selection.

                    What you want is to run the query in a CL program, and you want the query to retrieve only the rows that have the current date in the TISSD column.

                    Is this correct?

                    If so, then you can make the query itself retrieve the desired rows. Create two result fields:

                    Field Expression
                    CURRDATE current(date)
                    NUMDATE year(currdate) * 10000 +
                    month(currdate) * 100 +
                    day(currdate)

                    Then use NUMDATE in the Select Records screen.

                    Field Test Value
                    TISSD EQ NUMDATE

                    Then you can use the RUNQRY command in your CL program.


                    Code:
                    RUNQRY QRY(DATESELECT)

                    Comment


                    • #11
                      Originally posted by Lemon Grande View Post

                      Yes the current date, so far this is what i managed to do in order to get the current date from system and converted it to be the same format (YYYYMMDD)

                      Code:
                      PGM
                      
                      DCL VAR(&DATE) TYPE(*CHAR) LEN(10)
                      DCL VAR(&DATEFMT) TYPE(*CHAR) LEN(6)
                      CHKOBJ OBJ(JOHN/DATE) OBJTYPE(*DTAARA)
                      RTVSYSVAL SYSVAL(QDATE) RTNVAR(&DATEFMT)
                      CVTDAT DATE(&DATEFMT) TOVAR(&DATE) TOFMT(*YYMD) +
                      TOSEP(*NONE)
                      CHGDTAARA DTAARA(JOHN/DATE (1 12)) VALUE(&DATE)
                      
                      ENDPGM
                      I've yet to find the way to change the query field.
                      In a SQL query, you can use the CURRENT_DATE special register to avoid having to manipulate the date.

                      I'm not sure what you're asking when you wanted to know whether the CL commands RUNSQLSTMT and STRQM can be run using CL. If you're asking about running in batch, yes those CL commands can be run in batch.

                      Comment


                      • #12
                        Originally posted by TedHolt View Post
                        I probably don't understand what you're trying to accomplish, Lemon.

                        It seems to me that you have a query that you want to select records for the current date. You've used RTVQMQRY to get the SQL that retrieves the data so that you'll have a way to change the date used for record selection.

                        What you want is to run the query in a CL program, and you want the query to retrieve only the rows that have the current date in the TISSD column.

                        Is this correct?

                        If so, then you can make the query itself retrieve the desired rows. Create two result fields:

                        Field Expression
                        CURRDATE current(date)
                        NUMDATE year(currdate) * 10000 +
                        month(currdate) * 100 +
                        day(currdate)

                        Then use NUMDATE in the Select Records screen.

                        Field Test Value
                        TISSD EQ NUMDATE

                        Then you can use the RUNQRY command in your CL program.


                        Code:
                        RUNQRY QRY(DATESELECT)
                        It works ! Thank you very much

                        Comment

                        Working...
                        X