Concat with null string

[rforester]

I am trying to concatenate two fields in a query, but if one of the tow is empty, it is not returning anything.
Here is what I am using:
CONCAT( "OADDRESS1", "OADDRESS2" )

When OADDRESS2 is empty, it is returning an empty field.
How can I bring just OADDRESS1 when OADDRESS2 is empty?

Thanks!

[Sliderule]

Use EITHER ONE of the two statements below:

Code: Select all

CONCAT( "OADDRESS1", COALESCE("OADDRESS2",'') )


"OADDRESS1" || COALESCE("OADDRESS2",'')

Explanation: The SQL function COALESCE will return the second value ( in this case, a string with a lengh of zero ) when the first value is NULL ( UNDEFINED ). You can read about the COALESCE function at:

http://www.hsqldb.org/doc/1.8/guide/ch09.html

COALESCE(expr1,expr2,expr3,...)

if expr1 is not null then it is returned else, expr2 is evaluated and if not null it is returned and so on

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.

[Villeroy]

Any arithmetic or logical operation or concatenation with Null returns Null. The Coalesce function handles this by replacing Null with anything appropriate.

[rforester]

Thanks for the reply!

It should work but now it says:

The query can not be executed. It is too complex.

Any ideas?

[FJCC]

Try setting the option Edit -> Run SQL Command Directly.

[Villeroy]

Thanks for the reply!

It should work but now it says:

The query can not be executed. It is too complex.

Any ideas?

You did not mention the type of database indicated in the status bar. COALESCE runs fine with HSQLDB.

The minimal set of functions for file based databases (dBase, spreadsheets, text etc) does not include COALESCE.
http://www.openoffice.org/dba/specifica ... tions.html