Real Type

jeantawk · Post by **jeantawk** » Tue 19 Jun 2007 08:06

The type of an Aggregate function like (SUM , COUNT, NVL) is regarded as ` Integer ', even if it is applied to a field of the type ` FLOAT' .

Example 1 :

Query:
SELECT SUM(forecast_amount) FROM EP_BILL_DETAILS WHERE forecast_amount IS NOT NULL AND ROWNUM <= 1.

Result:

In‘Toad’ = 100.25
Via Odac = 100

i used "CAST as Flaot’ sur ‘SUM’ and also didn't work.

Can anyone help me with this issue.

Plash · Post by **Plash** » Wed 20 Jun 2007 08:52

We could not reproduce the problem. Please provide a script for creating EP_BILL_DETAILS table and Pascal code that you are using.
Also provide the following information
- exact version of ODAC including build number (see Oracle | About ODAC in the IDE menu);
- exact version of Delphi, C++Builder or Kylix;
- exact version of Oracle server and client. You can see it in the Info sheet of TOraSession Editor.

jeantawk · Post by **jeantawk** » Thu 21 Jun 2007 13:45

Sorry but i forgot to mention that i have made some changes in oraclasses for my apllication to work and due to this change i am having this problem. In the function GetFieldDesc8 i have made the change.

SQLT_NUM: begin
Check(OCIAttrGet2(hParam, OCI_DTYPE_PARAM, ValuePtr, nil, OCI_ATTR_PRECISION, hOCIError));
Prec := sb2(ValuePtr);
Check(OCIAttrGet2(hParam, OCI_DTYPE_PARAM, ValuePtr, nil, OCI_ATTR_SCALE, hOCIError));
Scale := sb1(ValuePtr);
Field.Length := Prec;
if FFieldsAsString or FNumberAsString then begin
if FConnection.FUseUnicode then
Field.DataType := dtWideString
else
Field.DataType := dtString;
Field.SubDataType := dtNumber;
Field.Scale := Abs(Scale);
if Prec > 0 then begin
if Scale = -127 then begin //FLOAT
Field.Length := Ceil(Prec * 0.30103);
Field.Size := 255; // string in fixed point format
end
else begin
Field.Length := Prec;
Field.Size := Field.Length + 3;
end
end
else begin
Field.Length := 38;
Field.Size := 46;
end;
if FConnection.FUseUnicode then
Field.Size := Field.Size * 2;
end
else begin
if (Scale = -127) and (Prec > 0) then //FLOAT
Field.Length := Ceil(Prec * 0.30103)
else begin //NUMBER
if Prec = 0 then
//Prec :=38; before
Prec := IntegerPrecision; //(9) after
if Scale = - 127 then
Scale := 0;
Field.Length := Prec;
end;
Field.Scale := Abs(Scale);
if FConnection.FEnableIntegers and (Prec FFloatPrecision) then begin
Field.DataType := dtNumber;
Field.Size := sizeof(IntPtr);
end
else begin
Field.DataType := dtFloat;
Field.Size := sizeof(Double);
end
end;
end;

In case of an integerField i have changed the Prec from 38 to IntegerPrecision which is 9 .

Is there any solution for this changement ?

Plash · Post by **Plash** » Fri 22 Jun 2007 07:32

Check whether data is selected correctly if you replace modified code with the following:

Code: Select all

            if Prec = 0 then
              if Scale = 0 then
                Prec := FIntegerPrecision
              else
                Prec := 38;

jeantawk · Post by **jeantawk** » Fri 22 Jun 2007 12:56

if Prec = 0 then
if Scale = 0 then
Prec := 38;
else
Prec := FIntegerPrecision ;

It did helped me but in the other way arround but there is new problem.
For example if the sum of a field is 100 the aggregate function sum gives the value 100.00 which is not appropriate. How can i fix this ?

Challenger · Post by **Challenger** » Wed 27 Jun 2007 11:25

According to OCI documentation: all Oracle number operations are accurate to the full precision. Oracle returns 0 for Precision and Scale for aggregate fields. So the changes you have made will not be compatible with aggregate fields.