controlling numeric rounding

[indyrk]

Hi,

I have a query where I am subtracting two numbers, and the result is always rounded, but I need it not to do that.

The two data fields are defined in the database as numeric(17).

My query looks like this:

select tmccomcred, baamount, case when tmcomcred<>0 then baamount - tmcomcred else 0.00 end as comm_taken from table

I've tried wrapping all my values in cast(colname as numeric(9,2)) to no avail.

Any suggestions?

Thanks

-Rob

 

[4GLNewbie]

First, have u tried to cast also the result to that value ( the entire subtraction )?

And it would be useful to know what db u have ( oracle, mysql, sqlserver, etc.. ) because different db may have different data conversion functions ..

Bye

 

[indyrk]

Thanks,

Yes, I have tried wrapping the subtraction statement in a cast function, no change.

The DB is Progress v9.

-Rob

 

[4GLNewbie]

Maybe using Decimal as a type will make it work better.

You can also try to use TO_CHAR function, i think, where u can specify a format of the string to output. It sounds like TO_CHAR( [field], '[format]' ). For example: TO_CHAR(baamount - tmcomcred, '99999,99')

Sorry but i am not able to test my suggestions on a progress db.
Hope it will help

 

[indyrk]

One thing about this query, the rounding only seems to happen inside the CASE statement.

So if the query is:
Select tmcomcred, baamount, baamount-tmcomcred, case when tmcomcred>0.00 then baamount-tmcomcred else 0.00 end from table

the first subtraction gives a decimal result, the second subtraction inside the case gives an integer!

Thanks for any help.

-Rob

 

[4GLNewbie]

Then i ask u to make this last try:
divide all your values by 1.00 ( i hope this will automatically put your values in a decimal format ) and see what happens.

To know how to use a conversion function, i think i ll have to let someone else to answer u. Search on PSDN, maybe there is something useful there.

Bye

 

[indyrk]

No luck with that! Thanks for your help though, I appreciate it. -Rob