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 - 601 days ago
Last viewed on: 26 Jun 2017 (1210 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:

 

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

 

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

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: what you see is not what is there.
Posted: 1 years 7 months 24 days 5 hours 41 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.

 

-Bob