Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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

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