what's the data type for a dividing result

[Former Member]

ex: I have a query select param1/86400 from [table1]

param1 is an integer

what's the data type of param1/86400, double or decimal?

[Former Member · Accepted solution]

I would guess that the context is important. If you e.g. insert the result in a DOUBLE column, then it will be casted to DOUBLE.

See also SAP Note 2176091 that describes a current problem with divisions and casting.

[lbreddemann · Accepted solution]

Thanks Martin, I was about to post the same SAP note.

Anyhow, the type casting and boxing is not thoroughly documented and actually changed (and changes) over different revisions.

Safest bet is to type cast operations when in doubt.

- Lars

[Former Member · Accepted solution]

Thanks, Martin.

I can't open the SAP Note.

do you mind pasting the summary here what's the problem with divisions and casting?

[Former Member · Accepted solution]

The SAP Note is released for customers, so hopefully you can see it when you check back in a few hours. The "cause" of the problem is:

The arguments of division of decimal(p,s) are casted to the expected result
precision and scale before the division, which can cause an uninteneded
overflow error when the divisor is larger than the expected result.

[Former Member · Accepted solution]

Martin,

do you mean even I have two integers ex, 47539/86400, the result will be decimal type and the s will be set befpre the division?

[Former Member · Accepted solution]

Please refer to the following test case from the SAP Note that should result in a numeric overflow due to the early casting:

create table test_decimal (a decimal(18,3), b decimal(18,0));

insert into test_decimal values ( 1693849628400, 18863570835);

select a/b from test_decimal;