Replace NULL with blank value OS400-CodePudding

I am trying to get my result set to return a blank rather than NULL, I have tried COALESCE but just cannot seem to get this to work, here is that section of my query:

CASE HHHCRIN WHEN 'Y' THEN HHHINVN ELSE '' END AS "Credit Memo Document Number",

CodePudding user response：

You have to start with the COALESCE command first to list your nulls, then include a CASE command to your resulting COALESCE new column to replace your nulls with blanks

CodePudding user response：

COALESCE works left to right and returns you the first non null thing encountered in that order;

SELECT
  COALESCE(name,address,'unknown value')
FROM Person

"If name is null, try address, if address is null, try 'unknown value' - and 'unknown value' is definitely not null"

For the following table:

Name,Address,  COALESCE returns
John,The Road, John
John,NULL,     John
NULL,The Road, The Road
NULL,NULL,     unknown value