| Editors: | Ted Holt | Managing Editor: | Mari Barrett | |
| Howard Arner | Technical Editor: | David Morris |
|
Volume 1, Number 23, sponsored by:Tramenco
|
|
|
|
|
|
How Do You Say Midnight in RPG? Hey, Ted: On a recent project, I was faced with the dilemma as to whether or not 00:00:00 is a valid time value on the AS/400. The reason for my quandary was triggered by a passage that I read in a popular RPG textbook. According to what I read, T'00:00:00' is a fictitious time. Would I ever get a time of 00:00:00? If so, how can I check for a NULL time (i.e., NO time)? Thanks for the help. -- Tim Both 00:00:00 and 24:00:00 are valid times. A time of *loval is the same as 00:00:00. A time of *hival is 24:00:00. Both stand for midnight. However, they are not identical: 0 hour is midnight at the beginning of the day. 24 hours is midnight at the end of the day. Furthermore, 0 hour at the beginning of one day is not the same as 24 hours at the end of the previous day. We have provided a couple of short programs that you can compile and play with if you would like to get a better idea of how RPG uses these two time values. In the first program, two time variables are initialized to '00:00:00' and '24:00:00'. (You may substitute *loval and *hival for the two time literals.) Then 1 second is added to the first time variable and 1 second is subtracted from the second time variable. Next, the program determines the number of seconds between the two time variables. Since there are 86,400 seconds in a 24-hour period, you will probably not be surprised when the elapsed time is two seconds less than that.
H datfmt(*iso)
D Time1 s t
D Time2 s t
D elapsed s 10u 0
C eval Time1 = t'00.00.00'
C eval Time2 = t'24.00.00'
C adddur 1:*s Time1
C subdur 1:*s Time2
C Time2 subdur Time1 elapsed:*s
C elapsed dsply
C eval *inlr = *on
The second program shows that 0 hour of one day is the same as 24 hours of the previous day, even though RPG does not consider them the same. In this program, Time1a and Time2a are set to midnight at the end of December 24 and midnight at the beginning of December 25, respectively. The two times are compared and found to be unequal. Next, 1 second is subtracted from those two times and the results are stored in variables Time1b and Time2b. These two values are compared and found to be equal.
H datfmt(*iso)
D Time1a s z
D Time1b s z
D Time2a s z
D Time2b s z
C eval Time1a = z'2001-12-24-24.00.00'
C eval Time2a = z'2001-12-25-00.00.00'
C if Time1a = Time2a
C 'a:equal' dsply
C else
C 'a:not equal' dsply
C endif
C Time1a subdur 1:*s Time1b
C Time2a subdur 1:*s Time2b
C if Time1b = Time2b
C 'b:equal' dsply
C else
C 'b:not equal' dsply
C endif
C eval *inlr = *on
With character variables, you can add the VARYING keyword and use a length of zero to represent null values. RPG doesn't allow zero-length variables of other data types, including date, time, time stamp, and the various numeric data types, nor does RPG allow you to place a special value in variables of those data types. We have two workarounds that you can use to store a null time value. The first is to code an additional Boolean-type variable for every variable that can have a null value. A Boolean variable can have only a true value or a false value. Boolean variables are implemented in RPG IV with the indicator data type, which is specified by a data type of n in the definition specification. If indicator variable TimeNull is on, then variable Time is considered to be null, regardless of its value. If TimeNull is off, then the value of Time is not null. Notice that it is the programmer's duty to look first at the null flag before using the Time variable. The following snippet demonstrates this technique:
D Time s z
D TimeNull s n inz(*on)
* Set time if it hasn't been initialized
C if TimeNull
C time time
C eval TimeNull = *off
C endif
In the second technique, use a basing pointer for null-capable fields. If the pointer is set to null, the Time field is considered null. The ALLOC opcode allocates storage and sets the pointer to a non-null value. If you need to set Time to null, use the DEALLOC keyword with an operation extender of (n). DEALLOC frees storage. Operation extender n tells DEALLOC to set the pointer back to null after the storage is successfully deallocated. The following snippet of source code demonstrates this technique:
D Time s z based(pTime)
D Size s 5i 0
* Set time if it hasn't been initialized
C if pTime = *NULL
C eval size = %size(Time)
C alloc size pTime
C time time
C endif
* Deallocate storage and set pointer to null
C if pTime <> *NULL
C dealloc(n) pTime
C endif
You can allow null values in fields of a database file regardless of data type, but that is a different question and answer. -- Ted and David
Hey, Ted: I am writing in reference to your article, "Looking Forward to Monday," which was published in the Midrange Guru, OS/400 Edition on December 7. Perhaps I'm seeing things too simply, but wouldn't it be easier to just use the AS/400 built-in job scheduler? Our company uses the Work with Job Schedule Entries (WRKJOBSCDE) screen to set up all types of jobs (i.e., overnight jobs, weekly jobs). The AS/400 seems to know what the day of the week is, so we have jobs set up that call programs to run on specific days. Keep up the great work with the newsletter! -- Tracy Thanks for reading the newsletter and for taking the time to write. Most of all, thanks for the encouragement. The guy who asked me the question is a programmer in a factory. He needed to store the scheduled completion date for a manufacturing order in a field in a database file, not schedule jobs to run on Mondays. Thanks again, Tracy, and I hope you enjoy the holidays! -- Ted
|  |
|
Hey, Howard: I have a question about SQL. Suppose I have a table containing sales for each day of a year, and it looks like the following example:
-- Muhammad This is a fun query because you get to take advantage of AS/400 date math, outer joins, and general goofiness! Muhammad, I am going to include all of the create table and insert statements so that you can try this example at home or at work. First, download a free SQLThing from www.sqlthing.com to execute the following commands, (or use Operations Navigator or interactive SQL if you are so inclined):
CREATE TABLE SQLBOOK/HTSALES (
SDATE DATE,
SALETOTAL DECIMAL(10,2),
PRIMARY KEY (sdate));
CREATE TABLE SQLBOOK/HTFISCAL (
PHYR SMALLINT,
PHWK SMALLINT,
SD DATE,
ED DATE,
PRIMARY KEY (PHYR, PHWK),
CHECK (SD
Note that I placed a check constraint into the HTFISCAL table to ensure that the SD field is always less than the ED field when a record is entered. Execute the following insert statements to place test data into your files:
/* Data for the HTFISCAL table */
INSERT INTO SQLBOOK/HTFISCAL VALUES (2000,39,'10/25/2000','10/31/2000');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2001,39,'10/27/2001','11/02/2001');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2000,40,'11/01/2000','11/07/2000');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2001,40,'11/03/2001','11/10/2001');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2000,41,'11/08/2000','11/14/2000');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2001,41,'11/11/2001','11/17/2001');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2000,42,'11/15/2000','11/21/2000');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2001,42,'11/18/2001','11/24/2001');
/* Data for the HTSALES table */
INSERT INTO SQLBOOK/HTSALES VALUES ('11/01/2000',5000);
INSERT INTO SQLBOOK/HTSALES VALUES ('11/02/2000',5500);
INSERT INTO SQLBOOK/HTSALES VALUES ('11/03/2001',5800);
INSERT INTO SQLBOOK/HTSALES VALUES ('11/04/2001',6000);
INSERT INTO SQLBOOK/HTSALES VALUES ('11/02/2001',7000);
Now, we are ready to cook. Consider the following query:
SELECT * FROM SQLBOOK/HTSALES a
INNER JOIN SQLBOOK/HTFISCAL c
ON (a.sdate >= c.sd
AND a.sdate <= c.ed);
If you're at V5R1, you can use this form, if you prefer:
SELECT * FROM SQLBOOK/HTSALES a INNER JOIN SQLBOOK/HTFISCAL c ON (a.sdate BETWEEN c.sd AND c.ed); This query asks for all of the fields from the HTFISCAL table and the HTSALES table and joins the two tables where the SDATE (the date of the sale) falls between the SD (start date) and ED (end date). By executing this statement, we now have a virtual table that consists of the total, date sold, accounting week, accounting year, accounting start period, and accounting end period. The results should look like this:
SELECT SDATE,SALETOTAL,C.SD,D.SD
FROM SQLBOOK/HTSALES a
INNER JOIN SQLBOOK/HTFISCAL c
ON (a.sdate >= c.sd
AND a.sdate <= c.ed)
INNER JOIN SQLBOOK/HTFISCAL d
ON (c.phwk = d.phwk AND c.phyr-1 = d.phyr);
V5R1 users may prefer to use BETWEEN in the join, like this:
SELECT SDATE,SALETOTAL,C.SD,D.SD
FROM SQLBOOK/HTSALES a
INNER JOIN SQLBOOK/HTFISCAL c
ON (a.sdate BETWEEN c.sd AND c.ed)
INNER JOIN SQLBOOK/HTFISCAL d
ON (c.phwk = d.phwk AND c.phyr-1 = d.phyr);
Notice that I am joining the HTFISCAL file to itself in order to find the previous year start date. The SQL statement should yield results similar to the following:
Now, it's time for some magic. Use date math to find the corresponding sales day for the previous year. This is done by calculating the previous sales date using the following expression:
PreviousSalesDate = (ThisSalesDate - ThisWeekStart) + LastYearStart Note that this will only work if the difference between the ThisSalesDate and the ThisWeekStart value is within a calendar month or so. This is because the AS/400 system returns results of date arithmetic in durations. However, for the purposes of this report, we should never have a start date greater than seven days from the sales date in order for it to work. If we did have a period greater than a month, we would have to use some complex SQL to account for the greater durations. Since, we don't need to do this, the query can remain simple. For more information on durations and date math, check my Web site, www.sqlthing.com, or purchase my book, "iSeries and AS/400 SQL at Work." So, to get the result, we just join again to the HTSALES table, but this time on our calculated previous-year sales date in order to yield a table of the correct data:
SELECT a.sdate, a.saletotal, b.sdate, b.saletotal
FROM SQLBOOK/HTSALES a
INNER JOIN SQLBOOK/HTFISCAL c
ON (a.sdate >= c.sd AND a.sdate <= c.ed)
INNER JOIN SQLBOOK/HTFISCAL d
ON (c.phwk = d.phwk AND c.phyr -1 = d.phyr)
LEFT OUTER JOIN SQLBOOK/HTSALES b
ON (b.sdate = (a.sdate-c.sd) + d.sd);
V5R1 installations may use this form instead:
SELECT a.sdate, a.saletotal, b.sdate, b.saletotal FROM SQLBOOK/HTSALES a INNER JOIN SQLBOOK/HTFISCAL c ON (a.sdate BETWEEN c.sd AND c.ed) INNER JOIN SQLBOOK/HTFISCAL d ON (c.phwk = d.phwk AND c.phyr -1 = d.phyr) LEFT OUTER JOIN SQLBOOK/HTSALES b ON (b.sdate = (a.sdate-c.sd) + d.sd); This SELECT statement uses a LEFT OUTER JOIN so that the AS/400 will return NULL for any dates and totals that do not exist in the previous year's data. Either version of the SELECT statement yields the following results:
See, joins and date math can be fun. I hope you enjoy it.
-- Howard
Subscription and Advertising Information
Subscription Information 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.
Advertising Information Please see our advertising opportunities and pricing at http://www.itjungle.com/advertising.html
Or contact Timothy Prickett Morgan at
Phone: 212 942 5818 Email: tpm@itjungle.com
If you have a tough problem, our gurus can probably help. Their mailboxes are always open. * Email Ted Holt at tholt@itjungle.com * Email Howard Arner at harner@itjungle.com | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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.
