Problem datatype Oracle 9.2.0.5

[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.

[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.

[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.

[Alex]

Try to use following code:

Code: Select all

SELECT CAST(SUM(IntField) AS NUMBER(3)) FROM MyTable 

[juan g.]

Unfortunately, this code also give me a Tfloatfield. I also tried to_number campo,3) and i got a floatfield...

[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...

[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).