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?

Fetal

Case expression, not statement... – Nocturn

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" 

Grandnephew

It's a bit wordy for the use case of substituting NULL values: Coalesce() is more idiomatic for PostgreSQL. – Retentivity

Progress is not Postgres ;) – Pettifer

COALESCE seems to exists for Progress as well. And is a lot cleaner - especially when you have more than one value to 'fall through' on null. documentation.progress.com/output/ua/OpenEdge_latest/… – Schulz

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" 

Fawcett

IFNULL is not a PostgreSQL SQL function. – Retentivity

@DavidAldridge It is Progress not PostgreSQL. progress4gl.com en.wikipedia.org/wiki/OpenEdge_Advanced_Business_Language – Fawcett

IFNULL is an ODBC function supported by the OpenEdge SQL-92 engine -- not a 4gl function or statement. (There is also an equivalent Progress OpenEdge SQL-92 extension: NVL( <expression>, <value> ) if you are not using ODBC.) – Pettifer

Thanks @TomBascom OPENQuery in SQLServer uses the linked server ODBC connection that might be reason IFNULL worked. – Fawcett

I would use COALESCE rather than IFNULL. documentation.progress.com/output/ua/OpenEdge_latest/… – Schulz