Calculated fields not showing decimal places in BASE query

users
#1

Hi Guys,

I am trying to create a report that shows the amount of rent overdue per
tenant.

I have a field in a table which holds the weekly rate (a decimal field ).

I am working out the number of weeks overdue by using the following formula

ABS( DATEDIFF( 'dd', "RENT_PAYMENTS"."Date_Paid", CURDATE( ) ) / 7 )

and then multiplying by the weekly rate to get the amount overdue but the
result never shows the trailing zeros.

i,e, 155.50 always shows up as 155.5

I have tried prefixing the formula with CAST (formula) AS DECIMAL (6,2)

Any ideas where I am going wrong

Thanks

John

#2

Hi :slight_smile:
How about formatting the cells to "Currency"?
Format - Cells - Numbers - Currency
and then explore the various options to tick or untick.
Regards from
Tom :slight_smile:

#3

Dividing an integer by another integer returns an integer.
Try this one:
ABS( DATEDIFF( 'dd', "RENT_PAYMENTS"."Date_Paid", CURDATE( ) ) / 7.00 )

#4

Hi Andreas,

adding the .00 to the divisor seems to screw up the result of the ABS
function

i,e, 13 days overdue = 1 week when dividing by 7
                                = 1.86 weeks when dividing by 7.00

regards

John

#5

Hi.

From the original posters example, he has decimal places, just not

trailing zeros. I think the calculation is correct just the display of
the result needs formatting. I don't use Base (yet) but is the correct
data type for currency DECIMAL. Can the field display format be set by
right clicking the field in the form as in a form letter.
steve

#6

Steve Edmonds wrote:

Hi.

From the original posters example, he has decimal places, just not

trailing zeros. I think the calculation is correct just the display of
the result needs formatting. I don't use Base (yet) but is the correct
data type for currency DECIMAL. Can the field display format be set by
right clicking the field in the form as in a form letter.
steve

The DATEDIF function returns an integer, 7 is an integer. So there is no
decimal involved in the calculation and the result is an integer despite the
division. No formatting attribute will change an iteger number. Any currency
format will end with .00 if the formatted value is an integer one.

How about this one with the last brace shifted:
ABS( DATEDIFF( 'dd', "RENT_PAYMENTS"."Date_Paid", CURDATE( ) ) ) / 7.00
(first get the absolute day difference, then divide by decimal 7.00)

#7

This gets weirder.

Try this: Create a table CONTRACTS with an INTEGER field (ID) as a
Primary key, set to auto fill
and a decimal field (Rental) set to 2 decimal places.

Add the value 150.00 to the first row.

Run the query

SELECT "CONTRACTS"."Rental" FROM "CONTRACTS"

this returns 150.00

Good so far.

Now run the query

SELECT 1.00, "CONTRACTS.Rental" FROM "CONTRACTS"

this returns 1.00 150

Any idea why?

regards

John

#8

This gets weirder.

Try this: Create a table CONTRACTS with an INTEGER field (ID) as a
Primary key, set to auto fill
and a decimal field (Rental) set to 2 decimal places.

Add the value 150.00 to the first row.

Run the query

SELECT "CONTRACTS"."Rental" FROM "CONTRACTS"

this returns 150.00

Good so far.

Now run the query

SELECT 1.00, "CONTRACTS.Rental" FROM "CONTRACTS"

this returns 1.00 150

Any idea why?

You are selecting the value 1 and the all the values in the Rental
column. Why it is not displaying the decimal places I do not know.
To select the ID field you need to

SELECT "ID", "Rentals" or *
FROM "Contracts"

#9

Sorry Planas, perhaps I have not explained clearly.

I do not want the ID column ( it had to be included to allow me to enter
data into the Rental column).

The example I gave was to show that if I did a select on the decimal field
only it displayed as a decimal
but if I preceded the decimal field in the query with another it displayed
as an integer ( no decimal places)

Hope this clears this up

regards

John

#10

Hi John,

SELECT 1.00, "CONTRACTS.Rental" FROM "CONTRACTS"

this returns 1.00 150

Any idea why?

IMHO, this could be either a bug in the LibO SQL parser, or maybe the
TYPE converter (jni_uno2java.cxx type converter that handles conversion
of data types between Uno and java types).

Best thing to do would probably be to open a bug report.

If you save that query, and then run it with SQL preprocessing switched
off in the query designer, does it work as expected, i.e. give you 1.00
and 150.00 ?

Alex

#11

Hi John,

SELECT 1.00, "CONTRACTS.Rental" FROM "CONTRACTS"

this returns 1.00 150

Any idea why?

OK, I tested further and this is IMHO a bug.

With SQL preprocessing on, i.e. the default LibO behaviour, if your
decimal has figures after the decimal point, e.g. value 167.34, then
your query will display the value correctly, however if the value is an
integer, then despite the "digits after decimal", it will not display
the double zero digits of a value such as 155.00.

With SQL preprocessing turned off, the trailing digits will be displayed
on the value fetched from the field, but then the manually entered 1.00
value gets interpreted as an integer and the trailing zeros removed !!!.

So the problem looks to be somewhere in both the LibO SQL parser and
TYPE identification (perhaps inter-related).

Alex