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
Advertisement
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.