Re: I'm in confusion of data type definition...

Jimmy wrote:

> Hello all,
>
> I have a question about the data definition of number data type.
>
> SQL> create table aaa (aaa number(20,5));
>
> Table created.
>
> TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION
> DATA_SCALE
> ----------------- ---------------------- --------------
> ------------------ ---------------------- ----------------
> AAA AAA
> NUMBER 22
> 20 5
>
> SQL> create view bbb as select sum(aaa) sum_aaa from aaa group by aaa;
>
> View created.
>
> TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION
> DATA_SCALE
> ----------------- ---------------------- --------------
> ------------------ ---------------------- ----------------
> BBB SUM_AAA
> NUMBER 22
>
> 1) Why the data length of AAA.AAA is 22? Default value of number
> data type? Is data length value is not meaningful to number data type?
> Only meaningful to char, varchar2, date ?

Not to date, either. Number has a default, internal precision of 38. Add one for the decimal point and one for th possible minus sign, and your 20 ends up at 22.

>
> 2) Why data precision and data scale of BBB.SUM_AAA is null? How can
> I determine the data precision and data scale of BBB.SUM_AAA?
>
> Thanks.
> Jimmy

Hmmm, suspect it's of less to no significance to the view - you didn't specify the columns, did you?
What use has a sum of a value when the sum probably doesn't fit in 20 positions anyway?

--
Met vriendelijke groet/kind regards,

Frank van Bortel
Technical consultant Oracle

Work:                                Home:
----------------------------------   ----------------------------
V&L Informatica BV                   Hunzestraat 4
Palandijn 3, 7521 PN Enschede        7555 WB Hengelo
PoBox 545, 7500 AM Enschede          (31)074-2425046
053-4341500