CASE expression with NULL value

sql select case openedge progress-db

14,003

Solution 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"

Solution 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"

14,003

Author by

Zonus

Updated on June 18, 2022

Comments

Zonus almost 2 years
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?
- jarlh over 8 years
  
  Case expression, not statement...
David Aldridge over 8 years

IFNULL is not a PostgreSQL SQL function.
David Aldridge over 8 years

It's a bit wordy for the use case of substituting NULL values: Coalesce() is more idiomatic for PostgreSQL.
Tom Bascom over 8 years

Progress is not Postgres ;)
Anup Agrawal over 8 years

@DavidAldridge It is Progress not PostgreSQL. progress4gl.com en.wikipedia.org/wiki/OpenEdge_Advanced_Business_Language
Tom Bascom over 8 years

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.)
Anup Agrawal over 8 years

Thanks @TomBascom OPENQuery in SQLServer uses the linked server ODBC connection that might be reason IFNULL worked.
gnud almost 6 years

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/…
gnud almost 6 years

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