Odds and Ends

Dear Readers:

Here's another installment of the "Odds and Ends" column. I think many of you will find something of interest. Enjoy.

-- Ted

Question:

Can SQL convert a zero value in a numeric date field to a null value in a select statement?

Answer:

If you are running V4R2 or later, you can use the NULLIF function. It was designed for converting special codes to nulls.

NULLIF returns a null value if two arguments match. If the two arguments do not match, it returns the first argument. The following SQL query addresses your question.

SELECT JESEQ, JEACCT,                         
   NULLIF(JEDATE,0) as edate, JEAMT FROM gljnl

NULLIF compares the value of JEDATE with zero. If JEDATE is not equal to zero, EDATE takes the value of JEDATE. If JEDATE is zero, EDATE is null.

By the way, do not confuse NULLIF with IFNULL; they are two different things.

Question:

Some of the API's mention something called the Lilian date. What is that?

Answer:

The Lilian date is the number of elapsed days since October 14, 1582, the date the Gregorian calendar was adopted. Lilian dates are named for Aloysius Lilius, one of the designers of the Gregorian calendar.

Question:

The IBM eserver iSeries Support Software Technical Document Web site shows how to change the System Request menu so that option 3 runs the Work with Job (WRKJOB) command instead of the Display Job (DSPJOB) command. I think this is a good tip--I would prefer WRKJOB so I can delete spool files--but two things bother me. First, I do not like to modify IBM objects. Second, I would prefer that most users not have access to WRKJOB because WRKJOB has a CL command line. Do you have any suggestions?

Answer:

I had to work around this limitation on a project where I was not allowed to modify message description CPX2313 in QSYS/QCPFMSG. Here's what I did.

First, I created a message file of my own.

CRTMSGF MSGF(MYLIB/MYMSGF)

Second, I copied message description CPX2313 from QCPFMSG to my new message file.

MRGMSGF FROMMSGF(QSYS/QCPFMSG) +
   TOMSGF(MYLIB/MYMSGF) SELECT(CPX2313)

Third, I used the Work with Message File (WRKMSGF) command to change the description of the message in my message file.

WRKMSGF MSGF(MYMSGF)

I chose option 12 (Work with message descriptions), followed by option 2 (Change), and keyed over DSPJOB.

Next, I edited my initial program, adding an Override with Message File (OVRMSGF) command.

OVRMSGF    MSGF(QCPFMSG) TOMSGF(MYLIB/MYMSGF)

If you use this approach, make sure the initial program remains in the call stack. I added a GO command to the end of the program to display the initial menu.

By the way, you can replace the DSPJOB command in the CPX2313 message description with any command you wish. Here's how to make System Request option 3 call QCMD.

Create a command that runs QCMD as its command-processing program. You won't need complete source, so you can use the @CMDDFTSRC member that was presented in the "Put Users on the Fast Track," September 13, 2002, issue of Midrange Guru.

CRTCMD CMD(MYLIB/MYCMDLINE) PGM(QCMD) SRCMBR(@DFTCMDSRC)   

Replace DSPJOB with MYCMDLINE when changing the message description. Be sure not to insert any new characters or delete any characters.

Here's a Freebie:

If you have not done so already, check out the V5R2 tips at the iSeries Information Center Web site. There you will find a number of tips on several topics. One tip I found useful is the CL command finder, which lets me locate CL commands by name, portion of a name, or description. It has options to view all commands, new commands, or changed commands. There are also locators for APIs, SQL messages, system reference codes, and much more.

Sponsored By SNAP-E BOOKS

Snap-eBooks is a new publishing company dedicated to the principle that great products and tools do not have to cost a lot! Snap-eBooks completely disagrees with the current industry practice that says that you should charge whatever the market will bear for AS/400 related products. We believe that keeping the prices of our products low makes them accessible and affordable for everyone in our industry including programmers, operators, administrators, managers, and pretty much anyone else. After all, why should a book cost $89? At that price, only companies or corporations can afford them. Our low prices also mean that you can purchase however many eBooks you need, and get them when you need them. Each eBook is full of step-by-step information that you can use to immediately learn a given topic in a short amount of time. Download the Lightning-Guide eReader and then take a look at our sample eBook, called Introduction to Visual Age for RPG, and check it out for yourself just how useful our products really are.