Re: [libreoffice-users] Base query - CONCAT function

Hi Robert,
Thanks for answering.
Your suggestion, however, do not work on my Opensuse Leap 42.3 /
LibreOffice 5.3.3.2 configuration. 

I use the Query design feature to enter

"Vorname" || IFNULL( ' & ' || "Partnervorname", ' ' ) || "Nachname"
 or

"Nachname" || IFNULL( ', ' || "Vorname", ' ')

Both above return 0 (yes, zero!) for every record.
Only the "Partnervorname" field has NULL values. I didn't expect
concatenation to be treated like 'multiplication', but I learn
something new every day! Neverthless, something is not right here.

However, 
COALESCE ( CONCAT( "Vorname", SUBSTR( ' & ', 1 ), "Partnervorname",
SPACE( 1 ), "Nachname" ), CONCAT( "Vorname", SPACE( 1 ), "Nachname" ) )

delivers the required result.

Cheers
Harvey




On Fri, 2017-09-15 at 15:02 +0200, Robert Großkopf wrote:
> Hey Harvey,
>
> > I wanted to use the CONCAT function in a Base query. However, it
> > seems
> > that if ONE of the elements of the list to be concatenated is NULL
> > then
> > the CONCAT function returns a NULL. Am I right? Is it a bug or a
> > feature?
>
> feature, because anythin, which is connected with NULL should be
> NULL.
>
> Do the following
>
> SELECT "surname"||', '||"forename" AS "name" FROM "table"
>
> for concatenate. You could concatenate more than two fields in this
> way.
> If one field is NULL all will be NULL.
>
> Now
>
> SELECT "surname"||IFNULL(', '||"forename",'') AS "name" FROM "table"
>
> will set '' for comma and forename if forename is NULL. Will only the
> surname will be shown, if forename is NULL.
>
> Regards
>
> Robert
> -- 
> Homepage: http://robert.familiegrosskopf.de
> LibreOffice Community: http://robert.familiegrosskopf.de/map_3

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted