[libreoffice-users] Base query - CONCAT function
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? Kind Regards Harvey Nimmo -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Base query - CONCAT function
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+unsubscr...@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
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+unsubscr...@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
Re: [libreoffice-users] Base query - CONCAT function
Hi Harvey, > 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. Which database do you use? Internal HSQLDB? My system here: OpenSUSE 42.2, LO 5.4.1.2. Have tested all this code for the Base-Handbook, so I am interested why it shouldn't work for you. Must say I didn#t test how it would work when entering the code in GUI mode, not switching to SQL mode. Regards Robert -- Homepage: http://robert.familiegrosskopf.de LibreOffice Community: http://robert.familiegrosskopf.de/map_3 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Base query - CONCAT function
On Fri, 2017-09-15 at 19:19 +0200, Robert Großkopf wrote: > Hi Harvey, > > > > > 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. > > Which database do you use? Internal HSQLDB? My system here: OpenSUSE > 42.2, LO 5.4.1.2. Have tested all this code for the Base-Handbook, so > I > am interested why it shouldn't work for you. Must say I didn#t test > how > it would work when entering the code in GUI mode, not switching to > SQL mode. > > Regards > > Robert > -- > Homepage: http://robert.familiegrosskopf.de > LibreOffice Community: http://robert.familiegrosskopf.de/map_3 I have a backend Mariadb 10 which I access directly using the using the MySQL connector. Cheers Harvey > -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Base query - CONCAT function
Hi Harvey,
>
> I have a backend Mariadb 10 which I access directly using the using the
> MySQL connector.
All right. With MariaDB (and MySQL) the two pipes (||) dont work.
But something like
SELECT CONCAT("Vorname, ' ',IFNULL("Partnervorname",' '),"Nachname") AS
"Name" FROM "Table"
should work.
Regards
Robert
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3
--
To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Base query - CONCAT function
On Fri, 2017-09-15 at 22:46 +0200, Robert Großkopf wrote:
> Hi Harvey,
> >
> >
> > I have a backend Mariadb 10 which I access directly using the using
> > the
> > MySQL connector.
>
> All right. With MariaDB (and MySQL) the two pipes (||) dont work.
>
> But something like
> SELECT CONCAT("Vorname, ' ',IFNULL("Partnervorname",' '),"Nachname")
> AS
> "Name" FROM "Table"
>
> should work.
>
> Regards
>
> Robert
Hi Robert,
you are right it works with CONCAT, but, isn't CONCAT inconsistent
here?
This seems to work as desired:
CONCAT( [Vorname], IFNULL( CONCAT( ' & ', [Partnervorname] ), '' ), '
', [Nachname] )
But shouldn't the CONCAT( ' & ', [Partnervorname] ), '' ) actually
return a NULL due to the '' (i.e. not a space, zero length)?
Cheers
Harvey
--
To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Base query - CONCAT function
Hi Harvey, > > But shouldn't the CONCAT( ' & ', [Partnervorname] ), '' ) actually > return a NULL due to the '' (i.e. not a space, zero length)? NULL: There is nothing inside, no string, no number, no data ... Empty: Could only be for strings - nothing to see, but its a string with zero length. Base will save for default all fields without content as NULL, not as fields, which are empty. The difference: You could concatenate empty fields with content of other fields without problems, because it recognizes an empty string. With NULL it won't work. The Help in LibreOffice and the GUI is missleading here, because Base won't save fields as empty fields but as NULL. Regards Robert -- Homepage: http://robert.familiegrosskopf.de LibreOffice Community: http://robert.familiegrosskopf.de/map_3 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Base query - CONCAT function
On Sat, 2017-09-16 at 18:31 +0200, Robert Großkopf wrote: > Hi Harvey, > > > > > > But shouldn't the CONCAT( ' & ', [Partnervorname] ), '' ) actually > > return a NULL due to the '' (i.e. not a space, zero length)? > > NULL: There is nothing inside, no string, no number, no data ... > Empty: Could only be for strings - nothing to see, but its a string > with > zero length. > > Base will save for default all fields without content as NULL, not as > fields, which are empty. The difference: You could concatenate empty > fields with content of other fields without problems, because it > recognizes an empty string. With NULL it won't work. > > The Help in LibreOffice and the GUI is missleading here, because Base > won't save fields as empty fields but as NULL. > > Regards > > Robert > Thanks, Robert. I had a feeling that would be the answer. Many thanks for helping me to clear that up. Your proposed syntax is certainly more economical than the COALESCE alternative. Cheers Harvey -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Base query - CONCAT function
On Sat, 2017-09-16 at 18:37 +0200, Harvey Nimmo wrote: > On Sat, 2017-09-16 at 18:31 +0200, Robert Großkopf wrote: > > > > Hi Harvey, > > > > > > > > > > > > But shouldn't the CONCAT( ' & ', [Partnervorname] ), '' ) > > > actually > > > return a NULL due to the '' (i.e. not a space, zero length)? > > > > NULL: There is nothing inside, no string, no number, no data ... > > Empty: Could only be for strings - nothing to see, but its a string > > with > > zero length. > > > > Base will save for default all fields without content as NULL, not > > as > > fields, which are empty. The difference: You could concatenate > > empty > > fields with content of other fields without problems, because it > > recognizes an empty string. With NULL it won't work. > > > > The Help in LibreOffice and the GUI is missleading here, because > > Base > > won't save fields as empty fields but as NULL. > > > > Regards > > > > Robert > > > > Thanks, Robert. I had a feeling that would be the answer. Many thanks > for helping me to clear that up. Your proposed syntax is certainly > more > economical than the COALESCE alternative. > > Cheers > Harvey Hi Robert, but as I now have discovered, the COALESCE command is the correct solution to cope with several alternative syntax combinations by concatenation. Each CONCAT expression in the COALESCE parameter list can represent one of the desired syntax combinations, always assuming that the successive expressions in the parameter list can 'disqualify' themselves by returning a NULL from CONCAT before reaching the desired combination. It strikes me as being quite an elegant solution. Many thanks for your help. Cheers Harvey -- To unsubscribe e-mail to: users+unsubscr...@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
