Hello,
Using 7.1.2.2 on W10 with MySql 8.0
I have a search form which is working fine (almost to my liking). The mainform data comes from the filter table and the subform’s data comes from SQL I had help on quite some time ago. When I open the SQL through the properties panel it displays fine in design view. When I click on View in SQL it tells me I have an error and I was hoping to understand where LO says the error is (as I noted the form works fine). I also wanted to add an ORDER BY and when I do the form can no longer load the data.
SELECT *
FROM “client”
WHERE (UPPER(“first”) LIKE UPPER(CONCAT(’%’,:Var_FN,’%’)) OR (:Var_FN IS NULL))
AND (UPPER(“last”) LIKE UPPER(CONCAT(’%’,:Var_LN,’%’)) OR (:Var_LN IS NULL))
Thanks!
Hello,
You need to consider that the Query GUI was originally developed for HSQLDB embedded and based mostly upon that syntax. When you place something in there it doesn’t like it complains with an error. You could report as a bug but consider all the different databases and syntax. Can’t say what is the precise item causing the problem. You can open in SQL (my preferred method always) by right clicking the query name and selecting Edit in SQL View.... I had no problems with this.
I also had no problem (again in SQL view) with adding an Order by clause on to the SQL. The form opened up just fine in the correct sequence. Tried with asc and desc.
Tested on Ubuntu 20.04.x with LO v7.1.3.2 using JDBC. Using a Native connector (direct) is another problem.
I found out why I am getting the error. LO doesn’t like UPPER. It prefers UCASE, but UCASE doesn’t work with MySQL.
Jody
I have no problem using UPPER in LO with MySQL.
Worked with all upper, lower and mixed case data.
hmm. strange.
One difference is you use Win 10. Don’t want to load MySQL on my Win 10. Dumped it a couple of years ago as my production DB.
But you can see it works on Linux.
Edit:
Also note that my answer does state I am using JDBC. Don’t know your connection type.