[Solved] How to display one or other field(s) in a listbox

[gkick]

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

[UnklDonald418]

Try this. I assumed "ID" was the Primary Key field for "tblClient"

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"

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.

[gkick]

@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

[UnklDonald418]

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.

[gkick]

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

[Villeroy]

This concatenates not null strings with a separator:

Code: Select all

COALESCE("VWZ1",'') || COALESCE(CHAR(10)||"VWZ2",'') || COALESCE(CHAR(10)||"VWZ3",'') || COALESCE(CHAR(10)||"VWZ4",'') AS "VWZ"

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.

[gkick]

Thanks Villeroy