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     ║
╚════════════════╩════════════════════╩═══════╩═════════╝

closed as off-topic by Colin 't Hart, mustaccio, Max Vernon, hot2use, Michael Green May 25 '18 at 12:03

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, mustaccio, Max Vernon, hot2use, Michael Green
If this question can be reworded to fit the rules in the help center, please edit the question.

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;

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