[Solved] error in some queries after database split

[phollox]

Hello community,

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

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

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

[chrisb]

phollox,
the where clause does not permit the use of an alias.
i changed the like clause a little. you may wish to alter this.
try the code below.

Edit: 19 Sep 2018 17:07 updated code to reflect the actual table names. 20 Sep 2018 20:47 removed single quote (last character of code) which will have given 'Malformed String Error'

Code: Select all

select
concat(
	coalesce(m."Surname" || ', ',''),
	coalesce(m."First Name",''),
	coalesce(', ' || m."Middle Name",'')
) "Full Name",
m."ID Number",
coalesce(z."search_term", '%' ) "Search"
from "members" m, "z_NameSearchTerm" z
where
upper(
	concat(
	coalesce(m."Surname" || ', ',''),
	coalesce(m."First Name",''),
	coalesce(', ' || m."Middle Name",'')
	)
)
like '%' ||
upper(
coalesce(z."search_term",'')
)
|| '%'

[Sliderule]

phollox:

Since you are now using either HSQL Version 2.3.4 or HSQL Version 2.4.1 rather than the default database - Embedded Database - HSQL 1.8.0.10 . . . I would like to suggest a few changes to your database to make life so much easier.

1. If you have not done it yet, please have available the HSQL documentation for the version you are using. I prefer have on my computer the PDF documentation, so, I can quickly to a search on the items you need.
2. I urge you to perform the two statements below, only need be done once for the database. These can be executed from the OpenOffice / LibreOffice Menu:
   
   Tools -> SQL...
   

   1. 
      SET DATABASE SQL CONCAT NULLS
      
      set database sql concat nulls statement
      
      <set database sql concat nulls statement> ::= SET DATABASE SQL CONCAT NULLS { TRUE | FALSE }
      
      When the property is TRUE, concatenation of a null value with a not-null value results in a null value. When the property is FALSE this type of concatenation result in the not-null value.
      
      Setting this property FALSE results in concatenation behaviour similar to Oracle or MS SQL Server.
      
      SQL Standard requires a NULL result.
      
      The property is TRUE by default.
      
      Only a user with the DBA role can execute this statement.
      
      This is equivalent to the connection property sql.concat_nulls.

      Code: Select all

      SET DATABASE SQL CONCAT NULLS FALSE
      

   2. 
      Collations
      
      A COLLATION is the method used for ordering character strings in ordered sets and to determine equivalence of two character strings.
      
      The system collation is called SQL_TEXT. This collation sorts according to the Unicode code of the characters, UNICODE_SIMPLE. The system collation is always used for INFORMATION_SCHEMA tables.
      
      The default database collation is the same as the system collation. You can change this default, either with a language collation, or with the SQL_TEXT_UCC. This collation is a case-insensitive form of the UNICODE_SIMPLE collation.

      Code: Select all

      SET DATABASE COLLATION "SQL_TEXT_UCC"
      

3. 
   CONCAT
   
   CONCAT ( <char value expr 1>, <char value expr 2> [, ...] )
   
   CONCAT ( <binary value expr 1>, <binary value expr 2> [, ...] )
   
   The arguments are character strings or binary strings. Returns a string formed by concatenation of the arguments. Minimum number of arguments is 2. Equivalent to the SQL concatenation expression <value expr 1> || <value expr 2> [ || ...] .
   
   Handling of null values in the CONCAT function depends on the database property sql.concat_nulls ( SET DATABASE SQL SYNTAX CONCAT NULLS { TRUE || FALSE } ). By default, any null value will cause the function to return null. If the property is set false, then NULL values are replaced with empty strings.

   Therefore, when concatenating, you will NOT have to be concerned with NULL values, since, the database back-end will return an empty string ( like you want ). So, make sure it ( SET DATABASE SQL SYNTAX CONCAT NULLS ) is set to FALSE.
   
   Additionally, with HSQL CONCAT function, you can concatenate multiple text strings, not just two. Alternatively, you can use the SQL standard of || ( two pipe characters ), which is my preference.

4. 
   CONCAT_WS
   
   CONCAT_WS ( <char value separator>, <char value expr 1>, <char value expr 2> [, ...] )
   
   The arguments are character strings. Returns a string formed by concatenation of the arguments from the second argument, using the separator from the first argument. Minimum number of arguments is 3. Equivalent to the SQL concatenation expression <value expr 1> || <separator> || <value expr 2> [ || ...] . The function ignores null values and returns an empty string if all values are null. It returns null only if the separator is null.
   
   This function is similar to a MySQL function of the same name.

5. Code: Select all

   -- Suggested code below after issuing the two commands above, using CONCAT_WS
   SELECT
      CONCAT_WS(',', 
                "members"."Surname", 
                "members"."First Name", 
                "members"."Middle Name")  AS "Full Name",
      "members"."ID Number",
      COALESCE ( "z_NameSearchTerm"."search_term", '%' ) AS "Search"
   FROM "members", "z_NameSearchTerm"
   WHERE "Full Name"  LIKE '%' || COALESCE ( "z_NameSearchTerm"."search_term", '%' ) || '%'
   

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[chrisb]

this topic has been tagged [Solved] without any indication as to how.
when the original poster declines to declare their chosen solution & the reasoning behind it then the community does not benefit.
there were two replies one from myself the other by Sliderule.

chrisb said the where clause does not permit the use of an alias

Sliderules suggested code contains an alias in the where clause.
if my statement is true then Sliderules code will fail.
if Sliderules code executes without error then my statement is false.
users of hsql 2.x deserve clarity in regard of this issue. the rights or wrongs of the responses which are always made in good faith are immaterial.

@Sliderule please try & make time to download & execute the queries in the linked attachment: http://www.mediafire.com/file/snftltez1 ... 4.zip/file
the attachment contains two small tables, three queries(the OPs, yours, mine), hsqldb.jar (2.3.4) the .odb & necessary database files.
i executed SET DATABASE SQL CONCAT NULLS FALSE and SET DATABASE COLLATION "SQL_TEXT_UCC" from menu:Tools>SQL.
in order for my query to display accurately it's necessary to execute SET DATABASE SQL CONCAT NULLS TRUE from menu:Tools>SQL.
hsqldb 2.4.1 produces identical results.

[Sliderule]

Chrisb:
You are correct . . . to a certain point.

I did modify ( in my original reply ) the OP's ( Original Poster ) Query to the the RIGHT of the LIKE portion of the WHERE clause, BUT I did NOT notice ( include ) what should be corrected to the LEFT of the LIKE.

Therefore, see below, for my suggestion ( without having to use UPPER ) . . . and . . . it is up to the OP to use whichever solution is best for the OP. I do agree with Chrisb, it certainly would be helpful, for other readers of the Forum, if user phollox would have explained the solution used.

Code: Select all

-- Suggested code below after issuing the two commands above, using CONCAT_WS
SELECT
   CONCAT_WS(',',
             "members"."Surname",
             "members"."First Name",
             "members"."Middle Name")  AS "Full Name",
   "members"."ID Number",
   COALESCE ( "z_NameSearchTerm"."search_term", '%' ) AS "Search"
FROM "members", "z_NameSearchTerm"
WHERE CONCAT_WS(',',
             "members"."Surname",
             "members"."First Name",
             "members"."Middle Name")  LIKE '%' || COALESCE ( "z_NameSearchTerm"."search_term", '%' ) || '%'

Sliderule

[chrisb]

Sliderule,
thank you for the clarification.
users of hsqldb 2.x can now be assured that use of an alias (as in "Full Name") is forbidden within the where clause.

your posts are always detailed, thorough, informative & superbly presented.
you often go beyond the remit of the topic & your updated code offers a full & complete solution.