0

In the customers' table, many customers do not have state data in the state column therefore when we select customers, the state column displays NULL values, which is not meaningful for the reporting purpose.

╔════════════════╦════════════════════╦═══════╦═════════╗
║ customerNumber ║    customerName    ║ state ║ country ║
╠════════════════╬════════════════════╬═══════╬═════════╣
║            103 ║ Atelier graphique  ║ NULL  ║ France  ║
║            112 ║ Signal Gift Stores ║ NV    ║ USA     ║
╚════════════════╩════════════════════╩═══════╩═════════╝

I am trying to improve the output by using the case, but getting an error:

SELECT 
    customerNumber,
    customerName,
    CASE 
      WHEN state IS NULL, THEN 'N/A'
      ELSE state
    END) as state,
    country
FROM
    customers;

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' THEN 'N/A' ELSE state END) as state, country FROM customers' at line 5

Desired Result:

╔════════════════╦════════════════════╦═══════╦═════════╗
║ customerNumber ║    customerName    ║ state ║ country ║
╠════════════════╬════════════════════╬═══════╬═════════╣
║            103 ║ Atelier graphique  ║ N/A   ║ France  ║
║            112 ║ Signal Gift Stores ║ NV    ║ USA     ║
╚════════════════╩════════════════════╩═══════╩═════════╝
3

You need to remove the comma from this part of your code:

CASE 
  WHEN state IS NULL, THEN 'N/A'
  ELSE state
END

The correct version would be:

CASE 
  WHEN state IS NULL THEN 'N/A'
  ELSE state
END

You can also use the coalesce function:

COALESCE(<VALUE>,<OUTPUT IF NULL>)   

The COALESCE function translates well this code:

CASE WHEN <value> IS NOT NULL
     THEN <value>
     ELSE <value_2>
     END

Coalesce returns the first non null value in the list.

Using COALESCE, your query will appear like this:

SELECT 
    customerNumber,
    customerName,
    COALESCE( state, 'N/A') as state,
    country
FROM
    customers;
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.