|Editors:||Ted Holt||Managing Editor:||Mari Barrett|
|Howard Arner||Technical Editor:||David Morris|
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?
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).
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?
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.
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?
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.
To unsubscribe, change your email address, or sign up for any of Guild Companies, Inc's free email newsletters, visit http://www.itjungle.com. Hit the SUBSCRIBE button on the homepage and it will lead you to our online subscription system.
When you sign up for one of our e-newsletters, you can be assured that your e-mail address will NEVER be sold to an outside company.
Please see our advertising opportunities and pricing at
Or contact Timothy Prickett Morgan at
Phone: 212 942 5818
If you have a tough problem, our gurus can probably help. Their mailboxes are always open.
* Email Ted Holt at email@example.com
* Email Howard Arner at firstname.lastname@example.org
This document may be redistributed freely and enthusiastically by email, but only in its unedited form. Thanks for your cooperation.
Midrange Guru is a registered trademark of Guild Companies, Inc. IBM, AS/400, iSeries, OS/400, and eServer registered trademarks of International Business Machines Corp. All other product names are trademarked or copyrighted by their respective holders.