CAST INTEGER TO DECIMAL

[Nik25]

I have a problem!

When running a statement, which will select a field which is calculated by three INTEGER fields, the result will be INTEGER, too.

expl1:
select a.int1*a.int2/a.int3 as res from a

I need the field as an decimal (18,2).

I tried it twice

expl2:
select cast((a.int1*a.int2/a.int3)as decimal (18,2)) as res from a

expl3:
select cast(a.int1 as decimal (18,2))*a.int2/a.int3 as res from a

Both times the resultfield res changed its type into decimal (18,2). But it was multiplicated by 100.

So when the operation was like 10*2/8 then
expl 1 result: 10*2/8=2 (TYPE INTEGER).
expl 2 result: 10*2/8=250,00 (TYPE DECIMAL (18,2))
expl 3 result: 10*2/8=250,00 (TYPE DECIMAL (18,2))

Now i thought it to be a good idea to divide the result by 100. But the new result was as the following:

expl4:
select cast((a.int1*a.int2/a.int3)as decimal (18,2))/100 as res from a

expl 4 result: 10*2/8=2,00 (TYPE DECIMAL (18,2))

Can someone tell me, how I make the system answer
10*2/8=2,50 (TYPE DECIMAL (18,2))

Thank you for your help

 

[tlbroadbent]

You need to convert at least one of the values to a decimal before performing calculations.

Implicit conversion: Multiply the 1st column by 1.0.
Select
Cast(1.0*a.int1*a.int2/a.int3 As Decimal(18,2)) as res
From a

Explicit conversion: Use Cast.
Select
Cast(Cast(a.int1 As Decimal(18,2))*a.int2/a.int3 As Decimal(18,2)) as res
From a

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Nik25]

Well, your tip is not that helpful, because the System answers with a result, which is multiplied by 100. I do not know how to resolve that problem and why this problem occurrs. I am working on a Teradata. Maybe it depend on that?

 

[baltman]

Try select (a.int1*a.int2/a.int3)+0.00 as res from a

Adding "zero" with the desired decimals works in FoxPro...

Brian

 

[tlbroadbent]

You are in an ANSI SQL forum and the answers provided are for ANSI compliant SQL. If the answers are not helpful it is because you are in the wrong forum. If your database isn't ANSI compliant, I suggest posting your question in forum328 - Teradata. Someone there might be able to give you a better answer. You might even find an answer in the product documentation or at the Teradata website. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.