DB2 SQL Syntax for CASE Statement

[ks01]

Hello ...

I'm looking for some advice on how to handle the following task.

I’m trying to get a value to display if the original value is NULL. Here is my SQL:

      CASE (B.USR_R_NM)                                              
           WHEN X'60'                                                
           THEN '***USER ' || RTRIM(A.USER_ADD_ID) || ' DELETED***'
           ELSE B.USR_R_NM                                           
      END AS USER_NM,

Here is the value for this 27-byte field:

-                          
644444444444444444444444444
000000000000000000000000000

I’ve also tried

      CASE (B.USR_R_NM)                                              
           WHEN NULL                                                 
           THEN '***USER ' || RTRIM(A.USER_ADD_ID) || ' DELETED***'
           ELSE B.USR_R_NM                                           
      END AS USER_NM,

However neither option seems to work successfully for me.

I would appreciate any feedback.

Thanks in advance!

 

[Truusvlugindewind]

Why don't you google for "COALESCE"?

select COALESCE(B.USR_R_NM,'USER DELETED' from ...

 

[papadba]

Or look n the IBM manual. . .

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/3.2.13

 

[ks01]

Hi everyone ...

Just an update, I found the following seems to work and uses the CASE statement:

CASE WHEN B.USR_R_NM IS NULL                                     
     THEN '*** USER ' || RTRIM(A.USER_ADD_ID) || ' DELETED ***'
     ELSE B.USR_R_NM                                             
 END AS USER_NM,

Thank you again for the suggestions!

 

[blom0344]

You do not need a case whatsoever when you learn what Coalesce does..

Ties Blom

 

[ks01]

I couldn't figure out what coalesce did.

 

[papadba]

Look here:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/3.2.13

The flashlight/tubelight near the top left is the search for the manual. Using table of contents as the keywords will show the link to the Table of Contents.