CASE expression with NULL value

Question

I'm struggling to understand how to check for a null value in a progress case expression. I want to see if a column exists and use that, if not use the fallback column. For example, William in first name would be over written by Bill in fn.special-char.

I've got the following query:

SELECT  
"PUB"."NAME"."LAST-NAME" as LastName,
   CASE fn."SPECIAL-CHAR"
     WHEN   is null  THEN "PUB"."NAME"."FIRST-NAME"
     ELSE   fn."SPECIAL-CHAR"
END as FirstName

FROM "PUB"."NAME"  
LEFT OUTER JOIN "PUB"."DAT-DATA" fn on "PUB"."NAME"."NAME-ID" = fn."DAT-SRC-ID" and 11 = fn."FLD-FIELD-ID" 

When I run the query I get:

ORBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error SQL statement at or about "is null then "PUB"."NAME"."FIRST-" (10713)

If I do a select * I see everything. It just doesn't like the null part. I can also change the when is null to when 'bob' and it works.

Is there something different I need to do to use a null value in a progress db query?

Answer 1

The shorthand variation of the case statement (case expression when value then result ...) is a shorthand for a series of equality conditions between the expression and the given values. null, however, is not a value - it's the lack thereof, and must be evaluated explicitly with the is operator, as you tried to do. In order to do this properly, however, you need to use a slightly longer variation of the case syntax - case when condition then result ...:

SELECT  
"PUB"."NAME"."LAST-NAME" as LastName,
   CASE WHEN fn."SPECIAL-CHAR" IS NULL THEN "PUB"."NAME"."FIRST-NAME"
     ELSE   fn."SPECIAL-CHAR"
END as FirstName

FROM "PUB"."NAME"  
LEFT OUTER JOIN "PUB"."DAT-DATA" fn on "PUB"."NAME"."NAME-ID" = fn."DAT-SRC-ID" and 11 = fn."FLD-FIELD-ID" 

Answer 2

Instead of CASE you can use IFNULL function in Progress 4GL.

SELECT  
"PUB"."NAME"."LAST-NAME" as LastName,
   IFNULL(fn."SPECIAL-CHAR", "PUB"."NAME"."FIRST-NAME") as FirstName

FROM "PUB"."NAME"  
LEFT OUTER JOIN "PUB"."DAT-DATA" fn on "PUB"."NAME"."NAME-ID" = fn."DAT-SRC-ID" and 11 = fn."FLD-FIELD-ID"