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.

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

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!

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