Embedded SQL - cast blank field to zeros

When I was still working with QS36f files, what I ended up doing is creating a fix program for many files (just about every one. sigh.) The program would z-adds every numeric field to itself and updates the record. (Yes, it was an RPGIII program)
I used to run this fix program in my CL before any RPG/400/IV/sql to make sure all data was valid. Over time, I corrected or eliminated old programs putting blanks in numeric fields.
I don't have gigantic files, so this worked for me and did not take too much extra time.


If you can control the QS36F files, you may only have to do this one. If old RPG II programs are still running and putting blanks, well, you need to keep the fix program around and run it every time.
---Dale







message: 5
date: Wed, 16 Sep 2009 11:02:53 -0500
from: "Tim Adair/prairiefarms" <TAdair@xxxxxxxxxxxxxxxx>
subject: Embedded SQL - cast blank field to zeros

I've searched the archives for this and haven't been able to find an answer. I inherited an SQLRPGLE program that reads (using SQL Fetch) an old QS36F file and writes certain fields to another file. Unfortunately, S/36 Environment still allows numeric fields to contain blanks (spaces). When it tries to do a fetch on a record with a blank numeric field, SQL quietly barfs on itself with status 22018, error -420. I've tried explicit casting in various forms and none of them work. I've also added and H-spec fixnbr(*zoned) which apparently has no effect on embedded SQL

Is there a simple way to get around this? I know I can do case logic for each numeric field -
case when substr(F00001,501,6) > '000000'
then substr(F00001,501,6)
else '000000'
end

But there are dozens of numeric fields in this file. And there are a multitude of programs similar to this, waiting in the wings.

Any help would be greatly appreciated. I'm running out of aspirin.

TIA.

Timothy Adair