Decimal mapping problem - IBM AS400

This is a discussion on Decimal mapping problem - IBM AS400 ; The price field in the DB2 UDB (DB2/400) record is defined (by DDS) as packed length 9 with 2 decimals. Importing a record with x'000042050F' in the price field to EXCEL shows correct data eg.: price = 420.50 But using ...

+ Reply to Thread
Results 1 to 5 of 5

Thread: Decimal mapping problem

  1. Decimal mapping problem

    The price field in the DB2 UDB (DB2/400) record is
    defined (by DDS) as packed length 9 with 2 decimals.
    Importing a record with x'000042050F' in the price
    field to EXCEL shows correct data eg.:

    price = 420.50

    But using ORACLE SQL PLUS select price from dbname shows:

    price = 420

    Any help is welcome!

    Regards, Martin

  2. Re: Decimal mapping problem

    On Nov 22, 9:59 am, Martin Hinze wrote:
    > The price field in the DB2 UDB (DB2/400) record is
    > defined (by DDS) as packed length 9 with 2 decimals.
    > Importing a record with x'000042050F' in the price
    > field to EXCEL shows correct data eg.:
    >
    > price = 420.50
    >
    > But using ORACLE SQL PLUS select price from dbname shows:
    >
    > price = 420
    >
    > Any help is welcome!
    >
    > Regards, Martin


    Does the DDS have any formatting options like EDTWRD? I remember using
    SQL over some BPCS files where the negative was suppressed. Can you do
    select price *100 from dbname ?

    Jonathan.

  3. Re: Decimal mapping problem

    Jonathan Bailey schrieb:


    > Does the DDS have any formatting options like EDTWRD?

    I tried both (with/without edtcde(j))
    >

    I remember using
    > SQL over some BPCS files where the negative was suppressed. Can you do
    > select price *100 from dbname ?
    >
    > Jonathan.


    Price*100 = 42050 is what i get then.

    Martin

  4. Re: Decimal mapping problem

    Apparently the column is being treated as either an integer or NUMBER
    type. Regardless of its data type, the value presented in a report is
    always formatted character data. Investigate the formatting rules for
    numerics; i.e. NUMBER types. What I found, and thus infer, is that...

    Default NUMWIDTH is 10, with ability to show negative. A nine digit
    numeric, the decimal separator, and a sign requires 11 characters to
    display. For example:
    ....+....1....+
    9999999.99-
    It is possible that to make all values fit formatted, the optimal
    choice is instead, by rounding, to present for example:
    ....+....1....+
    9999999-

    Try issuing: COLUMN PRICE FORMAT $9,999,990D99MI

    Some snippets of docs & links:

    http://download-west.oracle.com/docs...3.htm#BACHCABF
    http://download-west.oracle.com/docs...3.htm#i2699195
    http://download-west.oracle.com/docs...3.htm#i2699209

    NUMBER Columns
    For numeric columns, COLUMN FORMAT settings take precedence over SET
    NUMFORMAT settings, which take precedence over SET NUMWIDTH settings.

    See "SET NUMF[ORMAT] format" and "SET NUM[WIDTH] {10 | n}".

    To change a NUMBER column's width, use FORMAT followed by an element as
    specified in Table 13-1, "Number Formats".

    Table 13-1 Number Formats

    <>
    SQL*Plus formats NUMBER data right-justified. A NUMBER column's width
    equals the width of the heading or the width of the FORMAT plus one
    space for the sign, whichever is greater. If you do not explicitly use
    COLUMN FORMAT or SET NUMFORMAT, then the column's width will always be
    at least the value of SET NUMWIDTH.

    SQL*Plus may round your NUMBER data to fit your format or field width.


    Formatting columns:
    http://download-west.oracle.com/docs...7.htm#i1081036

    Formatting NUMBER Columns
    When displaying NUMBER columns, you can either accept the SQL*Plus
    default display width or you can change it using the COLUMN command.
    Later sections describe the default display and how you can alter it
    with the COLUMN command. The format model will stay in effect until you
    enter a new one, reset the column's format with

    COLUMN column_name CLEAR
    or exit from SQL*Plus.

    Default Display
    A NUMBER column's width equals the width of the heading or the width of
    the FORMAT plus one space for the sign, whichever is greater. If you do
    not explicitly use FORMAT, then the column's width will always be at
    least the value of SET NUMWIDTH.

    SQL*Plus normally displays numbers with as many digits as are required
    for accuracy, up to a standard display width determined by the value of
    the NUMWIDTH variable of the SET command (normally 10). If a number is
    larger than the value of SET NUMWIDTH, SQL*Plus rounds the number up or
    down to the maximum number of characters allowed if possible, or
    displays hashes if the number is too large.

    You can choose a different format for any NUMBER column by using a
    format model in a COLUMN command. A format model is a representation of
    the way you want the numbers in the column to appear, using 9s to
    represent digits.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    Martin Hinze wrote:
    > The price field in the DB2 UDB (DB2/400) record is defined (by DDS) as
    > packed length 9 with 2 decimals.
    > Importing a record with x'000042050F' in the price field to EXCEL shows
    > correct data eg.:
    > price = 420.50
    >
    > But using ORACLE SQL PLUS select price from dbname shows:
    > price = 420
    >
    > Any help is welcome!


  5. Re: Decimal mapping problem

    On Thu, 22 Nov 2007 14:47:39 +0100, Martin Hinze wrote:
    > Jonathan Bailey schrieb:
    >
    >
    >> Does the DDS have any formatting options like EDTWRD?

    > I tried both (with/without edtcde(j))
    > >

    > I remember using
    >> SQL over some BPCS files where the negative was suppressed. Can you do
    >> select price *100 from dbname ?
    >>
    >> Jonathan.

    >
    > Price*100 = 42050 is what i get then.


    So Oracle is truncating the decimal places for you. Fix Oracle.

    --
    I still want a phone with caller-IQ.
    -- Tanuki in the monastery

+ Reply to Thread