Query/400 and Record Selection by Date

Hey, Ted:

I was asked to create a Query/400 query that would take the current date and determine yesterday's date, last week's date, and three months from the current date, and select records using these dates. This can be done within Query/400, but only it requires you to define a lot of clunky result fields needed to determine relative dates, especially when you're looking at numbers in CYYMMDD format to represent dates. I have a more simple (and portable) solution.

My solution involves a little help from a one-record external physical file. Create a DDS file called TODAYSDATE to hold the current date and any number of relative dates:

 
 * ======================================================== 
 * ONE-RECORD FILE FOR RECORD SELECTION ON DATE FIELDS      
 * ======================================================== 
 * To create:                                               
 *     CRTPF   FILE(xxx/TODAYSDATE)                      
 *               SRCFILE(xxx/QDDSSRC)                        
 *               SRCMBR(TODAYSDATE)                         
 *     INZPFM  FILE(TODAYSDATE)                             
 *               TOTRCDS(1)                                 
 * ======================================================== 
                                                            
A          R CURDT                                          
A            TODAY          7S 0                            
A                                      COLHDG(' ' +         
A                                             ' ' +         
A                                             'Today')      
A                                      EDTWRD('   -  -  ')  
A            YSTRD     R               REFFLD(TODAY *SRC)    

Create a program to populate the date fields. I wrote my program in RPG IV.

 * To compile:
 *     CRTBNDRPG PGM(xxx/TODAYR) 
 *               SRCFILE(xxx/QRPGLESRC)
 *               SRCMBR(TODAYR)     
 
FTODAYSDATEUF   E             DISK                                
                                                                  
DSYSDATE          S               D   INZ(*SYS)                   
DYESTERDAY        S               D                               
DLASTWEEK         S               D                               
DNEXT3MON         S               D                               
                                                                  
C     *YMD          MOVE      SYSDATE       CURDAY            6 0 
C     SYSDATE       SUBDUR    1:*DAYS       YESTERDAY             
                                                                  
C     *YMD          MOVE      YESTERDAY     YESTRDY           6 0 
                                                                  
C     SYSDATE       SUBDUR    7:*DAYS       LASTWEEK              
C     *YMD          MOVE      LASTWEEK      LASTWK            6 0 
C     SYSDATE       ADDDUR    3:*MONTHS     NEXT3MON              
C     *YMD          MOVE      NEXT3MON      NEXT3M            6 0 
                                                                  
C                   READ      TODAYSDATE
C     CURDAY        ADD       1000000       TODAY
C     YESTRDY       ADD       1000000       YSTRD
C     LASTWK        ADD       1000000       LSTWK
C     NEXT3M        ADD       1000000       NXT3M
C                   UPDATE    CURDT              
C                   MOVE      *ON           *INLR

Add this program to the overnight job schedule, to refresh the file each night.

In Query, use the TODAYSDATE file as an input file selection. Since there's only one record in the file, you can link other input files with an *ALL join.

Then you can have your query search using any date relative to the runtime date, without having to prompt for a record selection. And many different Query reports can use the same input file, so you don't have to keep coming up with result fields that try to figure out date duration.

-- Bob Ellsworth

Let me add a few comments to Bob's explanation.

The *ALL join to which Bob refers is more properly known as the cross join or Cartesian product. Each record of one file is joined to every record of another file. We have presented cross joins in previous issues of this newsletter:

• "Dynamic Result Field In Query/400"
  
  
• "Joining on Incompatible Fields in Query/400"
  
  
• "Time for a Common Table Expression, Part 2"

If you have only two input files in your query, including the one-record TODAYSDATE file, specify a join type of 1 (matched records), press Enter, and key *ALL in the first FIELD blank of the "Specify How to Join Files" panel.

If you have three or more input files, specify join criteria for all the files except for the TODAYSDATE file. You will not use the *ALL keyword.

The one-record file technique Bob suggests is good; I've used it before. The more date fields you add, the more useful the file becomes.

I looked into writing a COBOL program for the benefit of the COBOL readers, but I don't know much about date math in iSeries COBOL and didn't have the time to learn. If any one wants to translate Bob's RPG program into COBOL, I'll be glad to publish it.

--Ted

Sponsored By WORKSRIGHT SOFTWARE

Do you need area code information? Do you need ZIP Code information? Do you need ZIP+4 information? Do you need city name information? Do you need county information? Do you need a nearest dealer locator system? We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you! The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year. PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal. Just call us and we'll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4. WorksRight Software, Inc. Phone: 601-856-8337 Fax: 601-856-9432 E-mail: software@worksright.com Web site: www.worksright.com