skip to Main Content

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


  1. 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/

    Login or Signup to reply.
  2. try COALESCE() e.g.

    COALESCE(Name, ‘N/A’) as Name

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search