Ask Your Question
1

Base|Firebird: CONCATENATE & COALESCE

asked 2021-04-28 17:42:40 +0200

lonk gravatar image

Fedora 34 Workstation Edition GNOME 40 DE

LibreOffice 7.1.2(.2) Base + Firebird

image description

Query1Concatenate works well.

SELECT "FruitCode" || "FruitName" || "FruitColor" || '*' "FruitConcatenate" FROM "Table1"

image description

Query2Coalesce is for fields maybe null.

SELECT COALESCE ( "FruitCode", "FruitName", "FruitColor", '*' ) "FruitCoalesce" FROM "Table1"

image description

Why doesn't it work as described in here ?

C:\fakepath\FirebirdCoalesce0024.odb

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2021-04-28 18:00:48 +0200

Ratslinger gravatar image

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:

image description

edit flag offensive delete link more

Comments

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 ?

lonk gravatar imagelonk ( 2021-05-01 10:26:47 +0200 )edit

Dear @Ratslinger,

Sorry for many comments.

I got it, "''" .

lonk gravatar imagelonk ( 2021-05-01 10:53:27 +0200 )edit

@lonk,

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

Ratslinger gravatar imageRatslinger ( 2021-05-01 19:13:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-04-28 17:42:40 +0200

Seen: 30 times

Last updated: Apr 28