I’m looking for guidance on how to apply IFNULL to an outer query in my mySQL Query. The query checks for new timecards submitted the previous day and kicks off a procedure that writes the results to a file server. Frequently there are no records but we still need to send a file with a record showing "no data". I believe the best solution is IFNULL but my syntex results in an error. Below is my raw query without the IFNULL outer query. Any help with syntex would be greatly appreciated.
SELECT
CONCAT(r.first_name, ' ', r.last_name) 'Name',
e.eng_no AS 'ENG_ID',
eb.po_number AS 'PO_NUMBER',
ne.org_name AS 'Supplier',
uv.full_name AS 'Timecard_Approver',
eb.po_line_number AS 'PO_Line_No',
rt.ext_project_code AS 'Ext_Project_Code',
rt.day_date AS 'Timecard_Date',
rt.job_work_site AS 'Job_Work_Site',
rt.worked_hours AS 'Total_Hours'
FROM
raw_tcd_agg rt
JOIN
eng e ON rt.eng_id = e.id
JOIN
res r ON r.id = e.res_id
JOIN
eng_brt eb ON eb.id = e.cur_eng_brt_id
AND eb.eng_id = e.id
JOIN
cli_ctr cc ON cc.net_id = e.net_id
AND cc.id = eb.cli_ctr_id
JOIN
usr_viw uv ON e.cli_usr_id_tcd_approver = uv.id
LEFT JOIN
net_ent ne ON e.ven_net_ent_id = ne.id
WHERE
e.net_id = 100
AND e.cli_net_ent_id = 1234
AND rt.day_date IN (SELECT
rtm.day_date
FROM
raw_tcd_agg rtm
JOIN
eng e2 ON e2.id = rtm.eng_id
WHERE
e2.net_id = 100
AND e2.cli_net_ent_id = 1234
AND rt.day_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
2
Answers
The command you want to sub a NULL for another value works like this:
IFNULL(expression, alt_value)
Use is expression set to the the variable to be tested for NULL, and the alt_value is the returned value instead, pretty simple.
Citation -> https://mysqlcode.com/mysql-isnull-ifnull/
try COALESCE() e.g.
COALESCE(Name, ‘N/A’) as Name