Page 1 of 1
Problem datatype Oracle 9.2.0.5
Posted: Thu 14 Apr 2005 15:17
by juan garcía
Hello!
I've created a TOraQuery that contains this sql:
Code: Select all
select
sum(campo) campo
from
tabla
where 'campo' is a column declared number(3) -it's would be TIntegerField. However when i request to odac the fields of dataset, odac says me the field campo is data type FloatField.
What's happen? Is it a error of ODAC or Oracle Server?
Thank you in advance.
Posted: Fri 15 Apr 2005 10:54
by Alex
In your case when you request Sum(IntegerField) the result of Sum may exceed integer precision, so Oracle returns 0 in precision description and based upon maximum compatibility ODAC assumes that this field must have maximum precision and consequently sets the field type to float.
Serious problem
Posted: Fri 15 Apr 2005 11:17
by juan
Then, is there any method to indicate on the sql the datatype of agregate column? It's is very important for me, so I replaced a table to view and this view contains agregate values on columns that are integer on the table.
But I don't found the method I will change a lot of fields of dataset.
Thanks.
Juan.
Posted: Fri 15 Apr 2005 12:03
by Alex
Try to use following code:
Code: Select all
SELECT CAST(SUM(IntField) AS NUMBER(3)) FROM MyTable
Also Floatfield
Posted: Fri 15 Apr 2005 12:16
by juan g.
Unfortunately, this code also give me a Tfloatfield. I also tried to_number campo,3) and i got a floatfield...
Re: Also Floatfield
Posted: Fri 15 Apr 2005 12:35
by juan g.
But when I make a "desc VIEW" the field is number(3), then why odac gives me a floatfield?. Example:
Code: Select all
CREATE OR REPLACE VIEW DELEGACION_0001001.ESTADISTICAS2
(a)
AS
select
cast(sum(CANTIDAD_ENTREGADA_1) as number(4)) a
from
estadisticas_t
Code: Select all
desc estadisticas2
Name Type Nullable Default Comments
---- --------- -------- ------- --------
A NUMBER(4) Y
Code: Select all
On odac: select * from estadisticas2
I gets a field "A TFloatfield".
Thanks you for any advice...
Posted: Tue 19 Apr 2005 11:05
by Alex
ODAC doesn't use SQL to describe fields, it uses OCI functionality and OCI returns 0 in field precision description for Views (it's look like an OCI bug - AFAIK such problem was fixed in 10g server).