Miscellaneous Query Tips

Dear Readers:

It appears that Query/400 is the software you love to hate. Despite its limitations and despite the other, and maybe even better, query products on the market, many of us appear to be stuck with it. Here are more miscellaneous Query/400 tips from your fellow readers.

--Ted

I enjoyed the article about date and time functions in Query/400. Here's a related tidbit.

The DIGITS function converts a number to a string but drops the sign. Converting the string back to a number (using the MICROSECOND function) gives you the absolute value of your original number.

It would look like this (assuming adaqty is a five-digit field):

ADJABSVAL   microsecond('2000-01-01-01.01.01.
            0'||digits(adaqty))

I used to think the DIGITS function dropping the sign was a flaw. Then I needed an absolute value function the other day, and it turned into a feature.

--Bill

I was impressed by the solution provided by your reader for converting a military time value into a standard 12 hour clock with AM/PM. I never knew you could use a field from a query as one of the numeric arguments of the SUBSTR function (in query), and using it for a table search is cool stuff. But there is another way to convert the time that I think is a little more straightforward.

The CHAR function will convert a time-type field into different formats, so all you have to do is convert your six-digit numeric field into a time-type value. That is accomplished with the TIME function, but it requires an alphanumeric value with the separator characters. No problem, either.

First you take your 6-byte numeric field and make it alpha, via the DIGITS function, like this:

TIMEA       digits(TIME6N)

Then you take that alpha value (TIMEA) and insert the colons; keep in mind that you will still be in a 24 hour clock:

TIMEFMT      substr(TIMEA,1,2) || ':' ||
             substr(TIMEA,3,2) || ':' ||
             substr(TIMEA,5,2)

Finally, you use TIMEFMT as the argument for the TIME function, which will turn that value into a time-type field, which then becomes the first argument of the CHAR funtion. Tack on the desired time format as the second argument of the CHAR command, like this:

TWELVEHRTM   char(time(timefmt),usa)

Here are some examples:

TIME6N   TIMEA   TIMEFMT   TWELVEHRTM
=======  ======  ========  ==========
152,536  152536  15:25:36  03:25 PM
114,855  114855  11:48:55  11:48 AM
114,434  114434  11:44:34  11:44 AM
112,526  112526  11:25:26  11:25 AM
      0  000000  00:00:00  00:00 AM
      0  000000  00:00:00  00:00 AM
143,651  143651  14:36:51  02:36 PM
132,320  132320  13:23:20  01:23 PM

Notice, though, that this solution does not zero-suppress the hour. Also, if you split the TWELVEHRTM field calculation into separate result fields, to first get a time-type field, like this:

TIMETYPE    Time(timefmt)

You can do time math on the number, using the HOURS function; for example:

PLUSEIGHT   timetype + 8 HOURS

In my first data record above (15:25:36 + 8 HOURS = 23:25:36), the +8 eight field can then be converted to 12 hour clock:

TIMEPLUS8    CHAR(PLUSEIGHT,USA)

Of course, a similar thing can be done to reverse YYMMDD values in file fields to print in MMDDYY order, as well as add durations to them.

Keep up the good work.

--John

A Query/400 query can use the current timestamp to generate a random number each time it runs.

RANDOM      MICROSECOND(CURRENT(TIMESTAMP))

The random number will be in the range 1 to 999999. To come up with a single random digit, take the least significant digit:

RANDOM1     MICROSECOND(TIMESTAMP('2000-01-01
            -12.00.00.00000'||
            substr(digits(random),6,1)))

Add a "Select Records" entry that selects records when RANDOM1 is not equal to one, and the report will fail to produce output 10 percent of the time. Great for practical jokes on your IT department. Maybe there's a valid business application, too. I can't think of one.

--Bob Ellsworth

Sponsored By INFORM DECISIONS

ELIMINATE THE COSTS OF PRE-PRINTED FORMS, LABOR AND POSTAGE WITH inFORM Decisions iDocs™ 'Suite' iSeries based e-Forms, e-Checks, e-Mail, e-FAX, and Document Retrieval from the Web are available as individual modules or as a complete e-Document processing 'Suite'. Click to Download the Complete Suite or Individual Modules today www.inFORMDecisions.com or call (800) 858-5544