Midrange News for the IBM i Community

Posted by: clbirk
what you see is not what is there.
has no ratings.
Published: 04 Nov 2015
Revised: 04 Nov 2015 - 3086 days ago
Last viewed on: 15 Apr 2024 (3209 views) 

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.

what you see is not what is there. Published by: clbirk on 04 Nov 2015 view comments(1)

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:






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 ....




Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Comment on: what you see is not what is there.
Posted: 8 years 5 months 12 days 11 hours 39 minutes ago

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.