Ask Your Question
0

Data to Fields not working for some calculated MySql query fields

asked 2017-02-22 16:30:05 +0200

wbaker gravatar image

updated 2017-02-22 16:35:23 +0200

In Writer/Data Sources window, I run a simple MySQL query with a single computation (sum): SELECT c.*, (c.client_income1+c.client_income2) AS total_income FROM client_incomes c

client_incomes table is just: Cl_ID client client_income1 client_income2

1 Red 1000 1500

2 Brown 2000 3000

Output, in Writer Data Sources window shows, correctly:

1 Red 1000 1500 2500

2 Brown 2000 3000 5000

Create a simple document by inserting all five fields: <cl_id> <client> <client_income1> <client_income2> <total_income>

Select top record and "Data to Fields" result in Writer document:
1 Red 1000 1500 <total_income>

Select top record and "Data to Fields" result in Writer document:
2 Brown 2000 3000 <total_income>

<total_income> field does not update or change.

I run the query in "Run SQL Command Directly" mode because it is actually part of a far more complex query that must be run directly.

If I define a stored function in MySql, say to add two numbers, and return that result as part of the query, the <total_income> field updates properly. However, my calculations are numerous and I really don't want to redefine and store simple math functions that already are available. Besides, some calculations are not simple in the original query.

My question(s): Is there a simple way to correct this behavior without having to store a bunch of math functions? It seems that "Data to Fields" is somehow not using or unable to use the calculated field. Is this a bug?

edit retag flag offensive close merge delete

Comments

1) Are you sure you're getting the data from the query, and not from the table? 2) Also, out of curiosity, what are the types for fields: Client_income1 and 2? Perhaps the result of the sum is of a type that is not correctly getting put into your table. Try using other numeric field types, like float, integer, decimal, etc. (Not saying what the solution is, but what you might try). (Note: native SQL connector has been removed from newer LO versions. Now MySQL/JDBC is way to go. Not JDBC/mysql.)

EasyTrieve gravatar imageEasyTrieve ( 2017-02-22 23:26:37 +0200 )edit

@EasyTrieve Thank you for this suggestion. It gives me a better direction. I will try them and report the results.

wbaker gravatar imagewbaker ( 2017-02-23 11:53:31 +0200 )edit

@EasyTrieve Right You Are! Replaced MySQL Native connector with MySQL JDBC (Connector J) and all is as it should be!

wbaker gravatar imagewbaker ( 2017-02-23 13:24:04 +0200 )edit

@wbaker, :-)

EasyTrieve gravatar imageEasyTrieve ( 2017-02-23 20:53:17 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2017-02-22 20:18:48 +0200

Ratslinger gravatar image

Using LO v5.3.0.3 currently (and others in the past), I have not been able to duplicate your situation - MySQL or otherwise.

Using some complex queries (including unions, multiple joins, calculations and more) the displayed information in the document always matches the information in the Data Source window.

Verify the <total_income> field in the document is in fact the one in the data source by hovering the mouse over the field. This will display the information source.

edit flag offensive delete link more

Comments

I'm using Ubuntu 16.04LTS, LO 5.1.4.2 with native MySQL connector. If I use MySQL functions that allow "Design View", the behaviour is correct. However, if I use slightly more sophisticated functions, where "Design View" is prevented because I must use "Run SQL Command Directly", the field never changes again. At any rate, I have confirmed that if I create a function to add two numbers and use it instead a simple"+", the "Data to Fields" operates correctly again.

wbaker gravatar imagewbaker ( 2017-02-22 21:57:58 +0200 )edit

Also, I've "hovered" over the <total_income> field and it is correctly used. I expected this as I inserted the field from the "other fields" dialogue. Your answer seems to indicate this is some quirk in my system, and maybe it is. I use three different computers (same software states, more or less) and all are behaving this way. Perhaps it is the connector. I will try a different connector next.

wbaker gravatar imagewbaker ( 2017-02-22 21:58:17 +0200 )edit

@wbaker My calculations also include simple + without problem. Have tried many connectors just for the sake of trying but have stuck with MySQL Connector/J 5.1.36. Did a lot of this testing today on Mint 18 system with & without Run SQL Command Directly.

Ratslinger gravatar imageRatslinger ( 2017-02-22 22:05:11 +0200 )edit

@Ratslinger Thank you for checking and trying to help. Your information is, in fact, helpful. I will dig a little deeper and add what I can find.

wbaker gravatar imagewbaker ( 2017-02-22 23:28:31 +0200 )edit
0

answered 2017-02-23 13:26:49 +0200

wbaker gravatar image

Thanks to EasyTrieve's suggestion (and Ritslinger's input), the problem is SOLVED. The odd behaviour only occurs when I was using the MySQL Native database connector. Replaced with MySQL JDBC (Connector J) and the "Data to Fields" operates perfectly as expected.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-02-22 16:30:05 +0200

Seen: 87 times

Last updated: Feb 23 '17