Oracle, numbers, 810, what to do?

[John T. Hunter]

Thanks for the suggestions from Blue and Christina,
but it appears that PH simply cannot handle Oracle
items defined as number(38).

I have asked the software vendor (of our Oracle database)
for suggestions and am awaiting a reply.

Here is a knowledge base article on the Cognos web site:

Title: Number (27) is the largest Oracle number that PH can access
Document ID:  KB73676CS
Product Version:  7.33
Operating System:  HPUX

Description:

An Oracle number column with a precision greater than 27 cannot be stored or
modified by PowerHouse.

Solution:

PowerHouse supports data item up to Packed Size 15. In Oracle this data type
is NUMBER(27). PowerHouse does its work in IEEE Float Size 8, which an
accuracy of 15 digits. Therefore, there is a good chance of lost accuracy in
Oracle number columns that are greater than 15.

If converting from another database, it is recommended that the metatdata
SQL be modified so that the columns have an appropriate size.

For example, INTEGER by default is mapped to NUMBER(38) in Oracle. On most
systems INTEGER is a four byte data item. NUMBER(38) creates a Packed Size
21 data column. So, rather than using the Oracle default, replace INTEGER
with NUMBER(9). This will produce the desired column size of four bytes. The
value specified in the NUMBER data item creates a check constraint in the
database. To use more than 9 digits for this column, NUMBER (x) would be
appropriate (where x > 9 and x < 17). A number in this range generates a
INTEGER SIZE 8 data column.
-----

Unfortuntitly I don't want to modify the Oracle side of things...

Thanks again,
-John

From: Hasse, Christina <Christina.Hasse@COGNOS.com>


>Hi John,
>
>I would try identifying the columns in the dictionary as elements with a
>size you determine and see if they work then.
>

From: Edis, Bob <bob.edis@fleetpride.com>

>G'day John
>
>Try
>
>ACCESS sku IN manu
>DEFINE np PACKED*20 = scen
>DEFINE nf FLOAT*20 = scen
>DEFINE nn = scen
>DEFINE nc CHAR*40 = ASCII(scen)
>SELECT IF NOT scen IS NULL AND scen NE 0
>REPORT np PIC " ^^^^^^^^^^^^^^^^^^^^" SKIP 1 &
>       nf PIC " ^^^^^^^^^^^^^^^^^^^^" SKIP 1 &
>       nn PIC " ^^^^^^^^^^^^^^^^^^^^" SKIP 1 &
>       nc
>GO
>
>One of these *should* work.  I have worked with large numbers in Oracle
>7.3.4 and as long as the PICTURE clause was used, I could see the contents.
>
>Have you tried SQL*Plus and DESCIBEd the table to see what Oracle has the
>column set as?
>


= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.