Decimal Fields displays negative on windows from IBM AS400

Question

I have a scenario where I do a select with SQL on the Green screen from a table on the as400(DB2), and I do the same SQL statement from a windows PC with a SQL tool.

On the as400(DB2) :

Notice the undrelined values 40229 and 41158. The Field type on the db2 side is DECIMAL 9.

Now When I run the same SQL on a windows PC using a Sql tool(IBM(R) Data Studio):

For some reason when the data gets returned as -40229 and -41158

It feels like an overflow of some sort but why and what?

When I navigate to the database structure from the Sql tool the field type is :

So on the as400 the fieldtype is DECIMAL 9 and from the Windows side its DECIMAL 9..

Why is this negative on Windows? Something to do with the type?

The SQL Windows : select * from maxdtaclb.szciexp where TCNUOR ='129444'

The SQL AS400: select * from maxdtaclb/szciexp where TCNUOR ='129444'

Did a select all where value is smaller than 0 and got results of 11 rows...

Again if I do a select on the green screen with the key of that same records it is negative???

Something I noticed is even if I use a rpgiv program to fetch the data from the table it is negative in the rpgiv program aswell..

Answer 1

If the file was created from a DDS source member (rather than an SQL DDL CREATE TABLE statement), it may be possible that the field was defined using the EDTCDE keyword. Edit codes can be used to control how numeric values are displayed in query output in an interactive session. Using an edit code of 1, 2, 3, or 4 will cause negative signs to be suppressed. If you use the field in an expression, SQL will generate a derived column that is no longer formatted by the edit code.

Example file definition in DDS source member::

            R RDMC001P                           
              NUMFLD1        5P 0       EDTCDE(3)

Example SQL session:

> insert into dmclib/dmc001p           
  values (-12345)                      
  1 rows inserted in DMC001P in DMCLIB.
> select NUMFLD1 from dmclib/dmc001p         
  NUMFLD1
   12345
> select decimal(numfld1,5,0) as NUMFLD2 from dmclib/dmc001p       
  NUMFLD2 
   12,345-

Here's a link to the 7.2 IBM manual describing the codes available for the EDTCDE DDS keyword. (It applies to physical files even though the title mentions display files.)