SQL where clause with a comparison-CodePudding

How it is posible to make a filter which is a comparison (a function)? So we can implement a filter like this

Because I got this console output:

ERROR: aggregate functions are not allowed in WHERE

WHERE MAX(vr.utz) > p_utz_begin AND fu.id_fl

this is the code.

SELECT  m_id_unit,
        lf.CAN_freq,
        lf.CAN_blackout,
        lf.GPS_freq,
        lf.GPS_blackout,
        lf.chargeloss
FROM tlm.main_dash_tele_freq_blackout(m_id_unit, p_utz_begin, p_utz_end) lf
JOIN var.vreadings vr ON vr.id_unit = lf.m_id_unit
JOIN dat.fleet_units fu ON fu.id_unit = lf.m_id_unit
WHERE MAX(vr.utz) > p_utz_begin AND fu.id_fleet <> 10

CodePudding user response：

You can not use aggregate functions with WHERE clause. Use HAVING clause to filter records if you require aggregation functions. See your database's documentation for HAVING and GROUP BY clauses to filter as you require in your current scenario.
For now, you may try the below snippet. In this snippet, I have used all of your columns as per your question in the GROUP BY, and added your condition in HAVING instead of WHERE.

SELECT  m_id_unit,
    lf.CAN_freq,
    lf.CAN_blackout,
    lf.GPS_freq,
    lf.GPS_blackout,
    lf.chargeloss
FROM tlm.main_dash_tele_freq_blackout(m_id_unit, p_utz_begin, p_utz_end) lf
JOIN var.vreadings vr ON vr.id_unit = lf.m_id_unit
JOIN dat.fleet_units fu ON fu.id_unit = lf.m_id_unit
Group by m_id_unit,lf.CAN_freq, lf.CAN_blackout, lf.GPS_freq, lf.GPS_blackout, lf.chargeloss
HAVING MAX(vr.utz) > p_utz_begin AND fu.id_fleet <> 10;

CodePudding user response：

The SQL HAVING Clause is used for aggregate functions. You also in order to use a MAX should include a group by. So something like this should work:

SELECT  m_id_unit,
        lf.CAN_freq,
        lf.CAN_blackout,
        lf.GPS_freq,
        lf.GPS_blackout,
        lf.chargeloss
FROM tlm.main_dash_tele_freq_blackout(m_id_unit, p_utz_begin, p_utz_end) lf
JOIN var.vreadings vr ON vr.id_unit = lf.m_id_unit
JOIN dat.fleet_units fu ON fu.id_unit = lf.m_id_unit
Group by m_id_unit,lf.CAN_freq, lf.CAN_blackout, lf.GPS_freq, lf.GPS_blackout, lf.chargeloss
HAVING MAX(vr.utz) > p_utz_begin AND fu.id_fleet <> 10

Here is a link to some info on using MAX with HAVING in case you have any questions: https://www.w3resource.com/sql/aggregate-functions/Max-having.php