create table in oracle 10g

[10goracle]

Hi,

I am trying to create a table in Oracle using a select query.

create table xxx as select AVG(column_name) from table_name;
create table xxx as select 0.12 from dual;

xxx is creating me a table with avg column values as integers in oracle 10g.
In 9i it creating with decimal values.

In Oracle 9i I am able to store all Decimal values by default even though
the table created is showing a type as NUMBER.

But when I am trying to do the same in Oracle 10g it is truncating all decimal
values to integers (ie., 0.95671 to 0) and storing it into the table.

If any one has a resolution to this in oracle 10g please let me know how can
I default a column to store a decimal value.

 

[SantaMufasa]

10g,

I don't see this as an Oracle 10g problem:

select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
...

SQL> create table table_name (column_name number);

Table created.

SQL> insert into table_name values (2.71);

1 row created.

SQL> insert into table_name values (7.8421);

1 row created.

SQL> insert into table_name values (9.1143);

1 row created.

SQL> select avg(column_name) from table_name;

AVG(COLUMN_NAME)
----------------
      6.55546667

SQL> create table xxx as select avg(column_name) avg_val from table_name;

Table created.

SQL> select * from xxx;

   AVG_VAL
----------
6.55546667

So, something else is causing your trouble.

What application software are you using to interface with Oracle?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Riddle me this:

  1  create table tabs  as select 4.61 colname from dual
  2  union select 3.2 from dual
  3* union select 5.234 from dual
SQL> /

Table created.

SQL> select * from tabs;

   COLNAME
----------
       3.2
      4.61
     5.234


SQL> create table avgtab as select avg(colname) avcol from tabs;

Table created.

SQL> select * from avgtab;

     AVCOL
----------
     4.348

SQL> *********************
SQL> select * from avgtab;

AVCOL
-----
    4

So what goes at the line of asterisksksks sks ks...
to get the result?

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 11 12:59:49 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options