sql - data type changes when using AVG in view definition - Stack Overflow

admin管理员组

文章数量:1418690

I have a table with a column of FLOAT data type. Based on this table, I create a view which returns AVG of that column.

create table my_tab (oo float);
create or replace view my_view (oo) as select avg(oo) from my_tab;

describing the view

desc my_view;

will return

Name Null? Type

---

OO NUMBER

If this an Oracle bug? Can I force somehow to maintain the source data type?

Thank you,

I have a table with a column of FLOAT data type. Based on this table, I create a view which returns AVG of that column.

create table my_tab (oo float);
create or replace view my_view (oo) as select avg(oo) from my_tab;

describing the view

desc my_view;

will return

Name Null? Type

---

OO NUMBER

If this an Oracle bug? Can I force somehow to maintain the source data type?

Thank you,

• sql
• oracle-database

Share Improve this question Follow asked Jan 29 at 13:49 mikcutumikcutu 1,09222 gold badges1919 silver badges3838 bronze badges 1

• Float is a subtype of number - it's just number with different semantics, whereby you specify the precision in in binary bits rather than decimal digits and with different limits. But the internal storage is the same. Whatever you can represent with float can be represented with number. All the aggregation functions (SUM, AVG, etc.) result in the supertype number when fed float. I personally haven't yet found any particular reason to use any numeric dataype other than simply number, except for integer as a short-hand way of setting the precision constraint (*,0). – Paul W Commented Jan 29 at 14:20

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

It is not necessarily a bug as it tries to be more precise using Number.

In the documentation it says :

A subtype of the NUMBER data type having precision p. A FLOAT value is represented internally as NUMBER.

Few other supporting docs.

You can still cast it to float as below :

create or replace view my_view (oo) as
select cast(avg(oo) as float) from my_tab;

Fiddle

本文标签： sqldata type changes when using AVG in view definitionStack Overflow