ISNULL giving "Unexpected Token"

[NickSamuel]

Here is the code:
SELECT “Notes1”.“Root Domain”, “Notes1”.“Contact”, ( “Notes1”.“Notes” || ( CASE WHEN “Notes2”.“Notes” IS NULL THEN “” ELSE “Notes2”.“Notes” END ) ) AS “Notes” FROM “Notes” AS “Notes1” LEFT OUTER JOIN “Notes” AS “Notes2” ON “Notes1”.“Root Domain” = “Notes2”.“Root Domain” AND “Notes1”.“Contact” = “Notes2”.“Contact” AND “Notes2”.“Stage” = 4 WHERE “Notes1”.“Stage” = 1

I had issues with CONCAT giving an out-of-range error (I was inserting padding in the concatenation) which || fixed. The idea is to see the “Notes1”.“Notes” when there is no “Notes2”.“Notes” - stopping the whole text being NULLed.

The message means nothing to me as I do not program in JAVA - “Unexpected token: in statement”

Is there a guide to the LibreOffice SQL syntax somewhere?

[Villeroy]

@NickSamuel

Is there a guide to the LibreOffice SQL syntax somewhere?

Base is not a database. It is a tool to work with databases, a frontend.
The type of connected database is indicated in the status bar of the main window of your database.
In case of “embedded HSQL”: Chapter 9. SQL Syntax
Your SQL snippet does not include any ISNULL. The test if something is null goes like "Something" IS NULL or NOT "Something" IS NULL respectively.

[NickSamuel]

Thank you for the link to the embedded HSQL dictionary. I have sorted the correct syntax now. (IFNULL not ISNULL etc.)

I had tried ISNULL and just expanded it the full if statement to see if it made any difference but it did not.

[Villeroy]

IFNULL("Column",'nothing','something')
is equivalent to
IF "Column" IS NULL THEN 'nothing' ELSE 'something' END

[Wanderer]

@NickSamuel

The idea is to see the “Notes1”.“Notes” when there is no “Notes2”.“Notes” - stopping the whole text being NULLed.

You may also read on function COALESCE() for this.