Mysql if Null or Empty String then Show N/A

Hello I’m new to mysqsl and I’m trying to display N/A if the return data is empty or NULL but my query is unrecognized statement type. What do you think is wrong with my query? wrong placement of code?

CASE when 'Earliest Time Opened' = '' or 'Earliest Time Opened' is 'Null'
 then 'N/A'
 ELSE
        SELECT (date_format(d.date_opened, '%r') as 'Earliest Time Opened'
        FROM  daily_report  d  JOIN userinfo ui
        ON d.userid= ui.id
        WHERE d.date_opened >= date_sub(curdate(), interval 0 day)
        AND d.survey_at_what_blh = 'Bagong Silang' AND ui.status='Employee'
        ORDER BY d.date_opened DESC
        limit 1

END

Answer

Use IF with ISNULL here:

SELECT IF(ISNULL(d.date_opened), 'N/A', DATE_FORMAT(d.date_opened, '%r')) AS `Earliest Time Opened`
FROM daily_report d
INNER JOIN userinfo ui ON d.userid = ui.id
WHERE
    d.date_opened >= CURDATE() AND
    d.survey_at_what_blh = 'Bagong Silang' AND
    ui.status = 'Employee'
ORDER BY
    d.date_opened DESC
LIMIT 1;

If you really wanted to use a CASE expression, then it would need to appear inside the select clause, something like this:

SELECT CASE WHEN DATE_FORMAT(d.date_opened, '%r') IS NOT NULL
            THEN DATE_FORMAT(d.date_opened, '%r')
            ELSE 'N/A' END AS `Earliest Time Opened`

But COALESCE is much more terse and appropriate here.

Leave a Reply

Your email address will not be published. Required fields are marked *