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?

Case expression, not statement... – jarlh Sep 01 '15 at 10:44 — jarlh, Sep 01 '15 at 10:44

score 10 · Accepted Answer · answered Aug 31 '15 at 21:44

10

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"

answered Aug 31 '15 at 21:44

Mureinik

297,002
52
306
350

It's a bit wordy for the use case of substituting NULL values: Coalesce() is more idiomatic for PostgreSQL. – David Aldridge Aug 31 '15 at 23:27
4

Progress is not Postgres ;) – Tom Bascom Sep 01 '15 at 00:08
2

`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. https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dmsrf%2Fcoalesce.html%23 – gnud May 30 '18 at 09:59

score 1 · Answer 2 · answered Aug 31 '15 at 21:41

1

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"

answered Aug 31 '15 at 21:41

Anup Agrawal

6,551
1
26
32

IFNULL is not a PostgreSQL SQL function. – David Aldridge Aug 31 '15 at 23:26
4

@DavidAldridge It is Progress not PostgreSQL. http://www.progress4gl.com/ https://en.wikipedia.org/wiki/OpenEdge_Advanced_Business_Language – Anup Agrawal Sep 01 '15 at 04:19
1

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( , ) if you are not using ODBC.) – Tom Bascom Sep 01 '15 at 13:58
Thanks @TomBascom `OPENQuery` in SQLServer uses the linked server ODBC connection that might be reason `IFNULL` worked. – Anup Agrawal Sep 01 '15 at 14:22
I would use `COALESCE` rather than IFNULL. https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dmsrf%2Fcoalesce.html%23 – gnud May 30 '18 at 10:00

CASE expression with NULL value

2 Answers2