Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
I have a table that was built 2 years ago, in which the first field (column) should have been defined character but it was defined numeric. It is like 12 long. It would have values in like either:
123456789012
or
123456789RYT
Now I chain this file with 123456789RYT (in a character field, not numeric), and I made hits just fine.
But when I look at the records what I see (wrkf or strsql) is 123456789983 (983 is RYT when you strip the zone).
So I am thinking that what we "see" is not necessarily what is written. I know I have had this issue with a file that has a null record yet I can't see any such record and it does not show up in the record count, etc.
This program has been running off this file for two years I discovered it today by accident, and rebuilt the table with the first column being character and dumping the data back in, and then went through and fixed those that got stripped.
I would have thought I would have seen it in wrkf but no...
thoughts besides beating the programmer who misdefined the sql column with a ....
chris
Zoned Numeric is really just a Character field with numeric restrictions.
The ZONE portion of the byte is ignored, except for the right-most digits/character.
All others are ignored, but are typically X'Fx'
The right-most digit can be x'Dx' negative or x'Fx' for possitive and one other value (that also means negative).
When RPG reads the data, it can "fix" the zoned decimal values, FIXNBR(*ZONED) so that it is valid numeric data.
SQL effective does the same thing, just differently and handled Decimal Data Errros differently.
-Bob