Converting Zeros to Nulls

In SQL, CASE can convert a zero to NULL in a select statement . I'm not sure at what release it became available, though [see the first question and answer in the February 12 issue of Midrange Guru].

It would look something like this:

SELECT JESEQ, JEACCT, 
     CASE WHEN JEDATE = 0 THEN NULL ELSE JEDATE END as edate, etc. . . 

This can also be used if there are multiple circumstances where you want a null result. And CASE itself will return a null if it has no ELSE and none of the WHEN criteria are met.

Keep the good stuff coming.

--Ken

NULLIF is just a shorthand version of CASE. The manual says it does a CASE under the covers. Both NULLIF and CASE were added to SQL/400 in V4R2.

Thanks for the encouragement.

--Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

E-Mail or FTP Files with Esend Send database, IFS or spooled files from your AS/400 or iSeries. Convert spooled files to PDF, HTML, PDF, and other formats. Burst and distribute reports by e-mail to any number of recipients. (Read more information) FREE Trial - Call 847-605-1311 or DOWNLOAD HERE