Ask Your Question
1

[base SQL] concatenate with empty fields?

asked 2016-07-17 16:03:57 +0200

MatthijsG gravatar image

The following SQL-code is working, but not for entry's with empty fields. If the field 'voorvoegsel' (dutch for prefix last name) is empty, there's no result. Other entry's are shown. If i use only the title (aanhef) and first name (voornaam) every entry is shown.

How can i get results when one field is empty?

SELECT 
"contacten"."Aanhef" ||' '|| "contacten"."Voornaam" ||' '|| "contacten"."Voorvoegsel" ||' '|| "contacten"."Achternaam" 
AS 
"volledigenaam" 
FROM "contacten" 
ORDER BY "contacten"."Voornaam" ASC
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-07-17 23:36:46 +0200

peterwt gravatar image

updated 2016-07-17 23:38:05 +0200

An empty text field is stored as null which prevents concatenation from working. If the only field that may be empty is voorvoegsel then the solution provided by @Ratslinger is fine, but if other fields can be empty then the case statement will become very long.

An alternative is to set the default value for any of the fields that can be empty to space in the Table. These fields will then never be null and the SQL you are using will work.

edit flag offensive delete link more
0

answered 2016-07-17 20:04:02 +0200

Ratslinger gravatar image

updated 2016-07-18 15:57:21 +0200

This will work by replacing the empty field with a value - in this case I used DATA MISSING. Hopefully I converted your statement correctly:

SELECT 
"Aanhef" ||' '|| "Voornaam" ||' '|| "Voorvoegsel2" ||' '|| "Achternaam" 
AS 
"volledigenaam" 
FROM (SELECT 
"contacten"."Aanhef", "contacten"."Voornaam", CASE WHEN "contacten"."Voorvoegsel" <> '' THEN 
"contacten"."Voorvoegsel" ELSE 'DATA MISSING' AS "Voorvoegsel2", "contacten"."Achternaam" 
FROM "contacten")
ORDER BY "contacten"."Voornaam" ASC

If I messed up the statement, here is the one tested with which works:

SELECT "NAME" || ' ' || "MYGROUP" || ' ' || "MYTYPE2" || ' ' || "MYVARIETY"
AS  "CONTACT" FROM (SELECT
NAME, MYGROUP, MYVARIETY, CASE WHEN MYTYPE <> '' THEN MYTYPE ELSE 'DATA MISSING' END AS MYTYPE2
FROM ANCIENTITEM) ORDER BY CONTACT ASC

Edit: A version which checks/replaces all NULL fields (shorter using COALESCE):

SELECT 
COALESCE("contacten"."Aanhef", 'Nee Aanhef') ||' '|| COALESCE("contacten"."Voornaam", 'Nee Voornaam') ||' '|| COALESCE("contacten"."Voorvoegsel", 'Nee Voorvoegsel') ||' '|| COALESCE("contacten"."Achternaam", 'Nee Achternaam')
AS  "volledigenaam" 
FROM "contacten" 
ORDER BY "contacten"."Voornaam" ASC
edit flag offensive delete link more

Comments

For some strange reason i can't get it to work. Must try your edit. Tnx so far!

MatthijsG gravatar imageMatthijsG ( 2016-07-20 20:40:50 +0200 )edit

@MatthijsG I hope you got this to work. If not, please specify which LO version, which OS and what DB (embedded, split, MySQL, etc.) you are using.

Ratslinger gravatar imageRatslinger ( 2016-07-22 17:23:31 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-07-17 16:03:57 +0200

Seen: 709 times

Last updated: Jul 18 '16