This is a follow up question to my previous post.
As I explained there, I used ExtractHSQL tool from Villeroy to use a more up-to-date driver in my LibreOffcie Database. I managed to connect it to version 2.3.4 as version 2.4.1 was giving connection issues. I now can see my tables and most of my queries. However, I have some search queries that takes a search term, saved in a placeholder table, and compares them with the full name of the members. I then use that as filter in the main form of some of my input forms. I'm getting an error similar to this one:
- Code: Select all Expand viewCollapse view
The data content could not be loaded.
SQL Status: 42501
Error code: -5501
user lacks privilege or object not found: Full Name in statement [SELECT CONCAT( COALESCE ( "members"."Surname", '' ), CONCAT( ', ', CONCAT( COALESCE ( "members"."First Name", '' ), CONCAT( ' ', COALESCE ( "members"."Middle Name", '' ) ) ) ) ) AS "Full Name", "members"."ID Number", COALESCE ( "z_NameSearchTerm"."search_term", '%' ) AS "Search" FROM "members", "z_NameSearchTerm" WHERE UPPER ( "Full Name" ) LIKE '%' || UPPER ( "Search" ) || '%']
I'm not sure if I did something non-standard in the constructon of those forms. I use table z_NameSearchTerm, which only has one cell, as a placeholder to hold my search term that compares with member's full name.
More clear, the query in question is as follows
- Code: Select all Expand viewCollapse view
SELECT
CONCAT( COALESCE ( "members"."Surname", '' ),
CONCAT( ', ',
CONCAT( COALESCE ( "members"."First Name", '' ),
CONCAT( ' ', COALESCE ( "members"."Middle Name", '' )
)
)
)
) AS "Full Name",
"members"."ID Number",
COALESCE ( "z_NameSearchTerm"."search_term", '%' ) AS "Search"
FROM "members", "z_NameSearchTerm"
WHERE UPPER ( "Full Name" ) LIKE '%' || UPPER ( "Search" ) || '%'
I'm not sure if this is the right forum for this. This query was working with the embedded 1.8 HSQLDB engine, and the issues started after the upgrade to 2.3.4
Thanks