Hello,
In a form I need to pick a client from a listbox. In the underlying table the cient may be stored in a field called company or in fields such as title,firstname, lastname.
So if the company field is blank the listbox should pick up title, first and last, whereas if there is no entry for first and last display the company instead - sort of a double coalesce ???
How can one do this ?
COALESCE("company",("title"| ' '||"cfname" || ' ' || "clname") FROM "tblClient" )
Above attempt failed miserably.
cheers
[Solved] How to display one or other field(s) in a listbox
[Solved] How to display one or other field(s) in a listbox
Last edited by gkick on Tue Jun 23, 2020 5:20 pm, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
-
- Volunteer
- Posts: 1546
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: How to display one or the other field(s) in a listbox
Try this. I assumed "ID" was the Primary Key field for "tblClient"
This looks a little odd, but beginning with HSQL 2.4.0, Base queries with UNION statements don't display the results unless you alias them.
Code: Select all
SELECT "A".*
FROM
(SELECT ("title" || ' ' || "cfname" || ' ' || "clname")"lbList", "ID" FROM "tblClient" WHERE COALESCE("company",' ') = ' '
UNION ALL
SELECT "company" "lbList", "ID" FROM "tblClient" WHERE "company" <> '' )"A"
ORDER BY "A"."lbList"
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: How to display one or the other field(s) in a listbox
@UnclDonald418,
Just brilliant, exactly what was needed ! Thank you. Only a small cosmetic thing, how can I eliminate the blank since I can not set both company and names to not null in the table.
Very much appreciated
Cheers
Just brilliant, exactly what was needed ! Thank you. Only a small cosmetic thing, how can I eliminate the blank since I can not set both company and names to not null in the table.
Very much appreciated
Cheers
- Attachments
-
- brilliant.png (7.57 KiB) Viewed 4542 times
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
-
- Volunteer
- Posts: 1546
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: [Solved]How to display one or other field(s) in a listbo
Check you data, the only way I have found to duplicate that issue is by entering a record in tblClient where both those fields are Null.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: [Solved] How to display one or other field(s) in a listb
Hmm, interesting, the data is clean, the query return the right results, only the listbox for some reason puts in a blank
no prob, just cosmetics, thanks
no prob, just cosmetics, thanks
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: [Solved] How to display one or other field(s) in a listb
This concatenates not null strings with a separator:
I copied this from my HSQL2 database. I have 4 textual annotation fields and concatenate the non-blanks into one field with CHAR(10) as separator.
COALESCE( separator || "Field", '' ) returns the empty string '' if "Field" IS NULL because any operation with NULL gives NULL. If all fields are Null, the expression concatenates 4 empty strings.
Code: Select all
COALESCE("VWZ1",'') || COALESCE(CHAR(10)||"VWZ2",'') || COALESCE(CHAR(10)||"VWZ3",'') || COALESCE(CHAR(10)||"VWZ4",'') AS "VWZ"
COALESCE( separator || "Field", '' ) returns the empty string '' if "Field" IS NULL because any operation with NULL gives NULL. If all fields are Null, the expression concatenates 4 empty strings.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] How to display one or other field(s) in a listb
Thanks Villeroy
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend