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