Calculated fields not showing decimal places in BASE query

[John Mullen]

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
--
Cheapest mobile tariffs - GUARANTEED <http://www.discoverhow.co.uk/>

"I train people to make money" <http://www.makemoney.discoverhow.co.uk/>

"Action always beats inaction" - Robert T. Kiyosaki

[Tom Davies]

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




________________________________
From: John Mullen <***@utilitywarehouse.org.uk>
To: ***@global.libreoffice.org
Sent: Tue, 6 September, 2011 15:50:44
Subject: [libreoffice-users] Calculated fields not showing decimal places in
BASE query

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
--
Cheapest mobile tariffs - GUARANTEED <http://www.discoverhow.co.uk/>

"I train people to make money" <http://www.makemoney.discoverhow.co.uk/>

"Action always beats inaction" - Robert T. Kiyosaki
--
For unsubscribe instructions e-mail to: users+***@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

[Andreas Säger]

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

--
View this message in context: http://nabble.documentfoundation.org/Calculated-fields-not-showing-decimal-places-in-BASE-query-tp3313752p3313940.html
Sent from the Users mailing list archive at Nabble.com.

[John Mullen]

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

Post by Andreas Säger
Dividing an integer by another integer returns an integer.
ABS( DATEDIFF( 'dd', "RENT_PAYMENTS"."Date_Paid", CURDATE( ) ) / 7.00 )
--
http://nabble.documentfoundation.org/Calculated-fields-not-showing-decimal-places-in-BASE-query-tp3313752p3313940.html
Sent from the Users mailing list archive at Nabble.com.
--
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
Cheapest mobile tariffs - GUARANTEED <http://www.discoverhow.co.uk/>

"I train people to make money" <http://www.makemoney.discoverhow.co.uk/>

"Action always beats inaction" - Robert T. Kiyosaki

[Steve Edmonds]

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

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

Post by Andreas Säger
Dividing an integer by another integer returns an integer.
ABS( DATEDIFF( 'dd', "RENT_PAYMENTS"."Date_Paid", CURDATE( ) ) / 7.00 )
--
http://nabble.documentfoundation.org/Calculated-fields-not-showing-decimal-places-in-BASE-query-tp3313752p3313940.html
Sent from the Users mailing list archive at Nabble.com.
--
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

[Andreas Säger]

Post by Steve Edmonds
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)

--
View this message in context: http://nabble.documentfoundation.org/Calculated-fields-not-showing-decimal-places-in-BASE-query-tp3313752p3314620.html
Sent from the Users mailing list archive at Nabble.com.

[John Mullen]

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

Post by Andreas Säger

Post by Steve Edmonds
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.
ABS( DATEDIFF( 'dd', "RENT_PAYMENTS"."Date_Paid", CURDATE( ) ) ) / 7.00
(first get the absolute day difference, then divide by decimal 7.00)
--
http://nabble.documentfoundation.org/Calculated-fields-not-showing-decimal-places-in-BASE-query-tp3313752p3314620.html
Sent from the Users mailing list archive at Nabble.com.
--
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
Cheapest mobile tariffs - GUARANTEED <http://www.discoverhow.co.uk/>

"I train people to make money" <http://www.makemoney.discoverhow.co.uk/>

"Action always beats inaction" - Robert T. Kiyosaki

[planas]

Post by John Mullen
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"

Post by John Mullen
regards
John

Post by Andreas Säger

Post by Steve Edmonds
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.
ABS( DATEDIFF( 'dd', "RENT_PAYMENTS"."Date_Paid", CURDATE( ) ) ) / 7.00
(first get the absolute day difference, then divide by decimal 7.00)
--
http://nabble.documentfoundation.org/Calculated-fields-not-showing-decimal-places-in-BASE-query-tp3313752p3314620.html
Sent from the Users mailing list archive at Nabble.com.
--
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
Cheapest mobile tariffs - GUARANTEED <http://www.discoverhow.co.uk/>
"I train people to make money" <http://www.makemoney.discoverhow.co.uk/>
"Action always beats inaction" - Robert T. Kiyosaki

--
Jay Lozier
***@gmail.com

[John Mullen]

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

Post by planas

Post by John Mullen
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"

Post by John Mullen
regards
John

Post by Andreas Säger

Post by Steve Edmonds
Hi.

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

trailing zeros. I think the calculation is correct just the display

of

Post by John Mullen

Post by Andreas Säger

Post by Steve Edmonds
the result needs formatting. I don't use Base (yet) but is the

correct

Post by John Mullen

Post by Andreas Säger

Post by Steve Edmonds
data type for currency DECIMAL. Can the field display format be set

by

Post by John Mullen

Post by Andreas Säger

Post by Steve Edmonds
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

Post by John Mullen

Post by Andreas Säger
decimal involved in the calculation and the result is an integer

despite

Post by John Mullen

Post by Andreas Säger
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.
ABS( DATEDIFF( 'dd', "RENT_PAYMENTS"."Date_Paid", CURDATE( ) ) ) /

7.00

Post by John Mullen

Post by Andreas Säger
(first get the absolute day difference, then divide by decimal 7.00)
--

http://nabble.documentfoundation.org/Calculated-fields-not-showing-decimal-places-in-BASE-query-tp3313752p3314620.html

Post by John Mullen

Post by Andreas Säger
Sent from the Users mailing list archive at Nabble.com.
--
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/

http://wiki.documentfoundation.org/Netiquette

Post by John Mullen

Post by Andreas Säger
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
Cheapest mobile tariffs - GUARANTEED <http://www.discoverhow.co.uk/>
"I train people to make money" <http://www.makemoney.discoverhow.co.uk/>
"Action always beats inaction" - Robert T. Kiyosaki

--
Jay Lozier
--
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

--
Cheapest mobile tariffs - GUARANTEED <http://www.discoverhow.co.uk/>

"I train people to make money" <http://www.makemoney.discoverhow.co.uk/>

"Action always beats inaction" - Robert T. Kiyosaki

[Alexander Thurgood]

Le 07/09/11 18:34, John Mullen a écrit :

Hi John,

Post by John Mullen
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

[Alexander Thurgood]

Le 07/09/11 18:34, John Mullen a écrit :

Hi John,

Post by John Mullen
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