CASE expression with NULL value

sql select case openedge progress-db
Zonus picture Zonus · Aug 31, 2015 · Viewed 11.2k times · Source

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

Mureinik picture Mureinik · Aug 31, 2015

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"

Related questions

TSQL CASE with if comparison in SELECT statement

I would like to use CASE statement in SELECT. I select from user table, and (as one attribute) I also use nested SQL: SELECT registrationDate, (SELECT COUNT(*) FROM Articles WHERE userId = Users.userId) as articleNumber, hobbies, ... FROM USERS and then …

Read More
Mysql SELECT CASE WHEN something then return field

I have two field nnmu and nnmi , if nnmu is equal to 1, I need to return naziv_mesta from **mesto_istovara**, else if it's =0 I need to return naziv_mesta from mesto_utovara table and reverse, if nnmi is equal …

Read More
SQL use CASE statement in WHERE IN clause

Is it posible to use case in where in clause? Something like this: DECLARE @Status VARCHAR(50); SET @Status='published'; SELECT * FROM Product P WHERE P.Status IN (CASE WHEN @Status='published' THEN (1,3) WHEN @Status='standby' THEN (2,5,9,6) WHEN @Status='deleted' THEN (4,5,8,10) …

Read More