Fedora 34 Workstation Edition GNOME 40 DE
LibreOffice 7.1.2(.2) Base + Firebird
Query1Concatenate works well.
SELECT "FruitCode" || "FruitName" || "FruitColor" || '*' "FruitConcatenate" FROM "Table1"
Query2Coalesce is for fields maybe null.
SELECT COALESCE ( "FruitCode", "FruitName", "FruitColor", '*' ) "FruitCoalesce" FROM "Table1"
Why doesn’t it work as described in here ?
Hello,
The statement did work as described.
The coalesce instruction takes the first non-NULL item. In your statement this was “FruitCode”. If you want to check and concatenate all those fields you need this:
SELECT COALESCE ( "FruitCode", '*' ) || COALESCE ( "FruitName", '*' ) || COALESCE ( "FruitColor", '*' ) "FruitCoalesce" FROM "Table1"
which produces this:
Dear @Ratslinger,
I replaced '*' with '', query in Firebird Base can run.
But when I moved the query to code:
"SELECT" & " " & _
"COALESCE" & " " & "(" & " " & """TB""" & "." & """FruitCode""" & "," & " " & """""" & " " & ")" & " " & "||" & " " & _
"COALESCE" & " " & "(" & " " & """TB""" & "." & """FruitName""" & "," & " " & """""" & " " & ")" & " " & "||" & " " & _
"COALESCE" & " " & "(" & " " & """TB""" & "." & """FruitColor""" & "," & " " & """""" & " " & ")" & " " & "||" & " " & "*" & _
"""FruitCoalesce""" & " " & _
"FROM" & " " & _
"""Table1""" & " " & """TB"""
The error message said :
Zero length identifiers are not allowed.
What should replace """""" in code ?
While it is possible your string may work it is most difficult to read and can be drastically improved upon.
Please see my answer and link within here → LO Base SQL Insert in Macro