Hey, Ted:

In our shop, we schedule work to be completed on the Monday of each week. Currently, a human keys the date of each Monday into a scheduling file. I'd like to do away with the file; there would be one less task to be done, one less thing that could go wrong. Scheduling is a task that I want to automate.

Rather than have RPG read a schedule file that is created by a human, I want my RPG programs to look at the current date and choose a schedule date. If the current date is Monday, then the schedule date is the current date. If the current date is not a Monday, then the following Monday is the schedule date. Is that possible?

-- David

Sure, if you use RPG IV. It's possible, David, in earlier versions of RPG as well, but a lot messier. Here's a routine that does what you ask. I'll let you decide how you want to implement it (i.e., as a standalone program, as a module, as a sub-procedure).

H datfmt(*iso)
                                                           
D currentDate     s               d   inz(*job)
D scheduleDate    s               d 
D elapsed         s              9p 0 
D adjust          s              1p 0 

C     currentDate   subdur    d'2001-11-05' elapsed:*D
C     elapsed       div       7             elapsed
C                   mvr                     adjust
C                   if        adjust > 0
C                   eval      adjust = 7 - adjust
C     currentDate   adddur    adjust:*d     scheduleDate
C                   else 
C                   eval      scheduleDate = currentDate
C                   endif 
C     scheduleDate  dsply 
C                   eval      *inlr = *on 
C                   return 

The first line of calculations finds the number of elapsed days between the job date and November 5, 2001. There is nothing special about November 5. It is just happens to fall on a Monday. Any Monday date will do. The divide (div) and move remainder (mvr) operations load a value from 0 to 6, representing Monday through Sunday, in the adjust field.

If the remainder is 0, there is no adjustment and you've got your Monday date. If the remainder is not 0, the calculations following the if statement will adjust the date.

One more point to consider is how I derived the current date. In this example, I used the job date, which facilitates rerunning a failed job. It can cause problems if the job calling this routine runs past midnight, because a job date remains the same for the duration of a job unless you specifically change it. An alternative solution is to use the system date, which does change. To use the system date in this program, replace inz(*job) with inz(*sys).

-- Ted

SPONSORED BY LANSA LANSA INTEGRATOR SPEEDS DEVELOPMENT & PERFORMANCE OF JAVA LANSA Partner MDC replaced its hand-written Java remote system integration programs with LANSA Integrator and gained a tenfold increase in performance. InsureIT uses LANSA Integrator to offer XML Java services between heterogeneous insurer & broker systems. Find out more at http://www.lansa.com/press/20122.htm

I'm writing regarding the article that addresses using SQL functions in SQL, "Extracting Packed Data from a Character Field" [October 19]. Can Query/400 access user-defined SQL functions?

-- Rob

Query/400 can access any SQL/400 feature that can be put into a view, Rob. When you run the SQL CREATE VIEW command, the system builds a logical file, and you can access that logical file from Query.

Here's how you'd build a view using the GetDec_03_00 function in the article that you mentioned:

CREATE VIEW mylib/myview AS                       
 select key, 
     getdec_03_00(substr(arr,1,2)) as num1, 
     getdec_03_00(substr(arr,3,2)) as num2, 
     getdec_03_00(substr(arr,5,2)) as num3, 
     getdec_03_00(substr(arr,7,2)) as num4, 
     getdec_03_00(substr(arr,9,2)) as num5  
   from decdata   
    

The logical file is called MYVIEW and is in library MYLIB. (I'm sure you will come up with names that are more descriptive.) Query/400 can read MYVIEW.

The view can also include SQL functions that IBM furnishes and calculated functions, such as multiplying two fields to get a value.

-- Ted

Hey, Ted:

How can I make CL do date arithmetic? I can make a CL program retrieve today's date. How can it make it figure out tomorrow's date?

-- Buddy

When it comes to date arithmetic, CL isn't even in first grade. As we approach the end of a calendar year, that is a very good and timely question, Buddy. CL has no date arithmetic ability whatsoever. Nevertheless, here is some code that will find tomorrow's date for you:

 PGM
   DCL  VAR(&TODAY)     TYPE(*CHAR) LEN(6)                  
   DCL  VAR(&TOMORROW)  TYPE(*CHAR) LEN(6)                  
   DCL  VAR(&WORKDATE)  TYPE(*CHAR) LEN(7)                  
   DCL  VAR(&YEAR)      TYPE(*DEC)  LEN(4)                  
   DCL  VAR(&DAY)       TYPE(*DEC)  LEN(3)                  
                                                            
   RTVJOBA    DATE(&TODAY)                                  
   CVTDAT     DATE(&TODAY) TOVAR(&WORKDATE) FROMFMT(*JOB) + 
                TOFMT(*LONGJUL) TOSEP(*NONE)                
 /* Find tomorrow's date */                                 
   CHGVAR  &DAY                 %SST(&WORKDATE 5 3)         
   CHGVAR  &DAY                (&DAY + 1)                   
   CHGVAR  %SST(&WORKDATE 5 3)  &DAY                        
   CVTDAT    DATE(&WORKDATE) TOVAR(&TOMORROW) +             
               FROMFMT(*LONGJUL) TOFMT(*JOB) TOSEP(*NONE)   
   MONMSG CPF0555 EXEC(DO)
     CHGVAR    &YEAR                %SST(&WORKDATE 1 4)      
     CHGVAR    &YEAR               (&YEAR + 1)               
     CHGVAR    %SST(&WORKDATE 1 4)  &YEAR                    
     CHGVAR    %SST(&WORKDATE 5 3)  '001'                    
     CVTDAT     DATE(&WORKDATE) TOVAR(&TOMORROW) +           
                  FROMFMT(*LONGJUL) TOFMT(*JOB) TOSEP(*NONE) 
   ENDDO                                                     
   /* Tomorrow's date is now in &TOMORROW */
 ENDPGM

This code converts the job date to a Julian number (which is a four-digit year followed by a three-digit day between 1 and 366), increments the Julian number, and converts the new Julian number to a date. On the last day of the year, the date conversion will fail, in which case the program will use day one of the next year.

-- Ted

SPONSORED BY WORKSRIGHT SOFTWARE, INC. WorksRight Software, Inc. announces that its iSeries and AS/400 postal coding software product, PER/ZIP4, has been recertified by the US Postal Service for CASS (Coding Accuracy Support System) processing. In addition, when used in conjunction with the PER/SORT mailing presort software, PER/ZIP4 has been certified for LOT (Line Of Travel) processing. PER/ZIP4 matches mailing addresses to the national ZIP+4 database. Matched addresses are formatted to postal standards and ZIP+4 and delivery point codes are added. As a result of this process, mailing files can be used as part of the qualification effort for significant postage discounts. WorksRight Software, Inc. is the leading provider of ZIP Code, mailing, and telephone area code software to the iSeries and AS/400 community. PER/ZIP4 is available for a free 30-day trial. To order a free trial, contact WorksRight by phone at 601-856-8337, by e-mail at software@worksright.com, or through the Web at http://www.worksright.com.