I have this somewhat complex sql query that works ok without the final where clause. I'm looking to filter some records using the column unreviewed_records which is an alias

Problem is that I get an error saying unreviewed_records cannot be found. I found some information saying that alias fields are not permitted to be used in where clauses and I'm not sure what's the best way to fix this. Considered using a computed column but I'm not sure how that works yet and I'm hoping there's an easier fix to the query. Also I find that switching to using the "having" clause work for aliases, but I'll only resort to this if there's no better alternative, to avoid the performance hit.

Any pointers would be helpful :)

select 
        r_alias.serv_id, r_alias.node_id,
            SUM(g_alias.total_records)- SUM(r_alias.reviewed_records) AS unreviewed_records,

            SUM(r_alias.reviewed_records) AS reviewed_records,
            SUM(g_alias.total_records) AS total_records,
        FROM (
        SELECT prs.serv_id,
               prs.node_id,
               SUM(prs.reviewed_records) AS reviewed_records,
        FROM p_rev_server prs
        WHERE
             prs.area_id = 3
            AND prs.subId = 3
            AND prs.sId = 12
            
        GROUP BY prs.serv_id, prs.node_id, prs.domain_name
    ) r_alias
        INNER JOIN (SELECT
               serv_id,
               node_id,
              SUM(pgs.total_records) AS total_records,
            FROM p_gen_serve pgs
            WHERE pgs.area_id = 3
            AND pgs.subId = 3
            AND pgs.sId = 12
            AND pgs.total_records > 0
            
            GROUP BY pgs.serv_id, pgs.node_id, pgs.domain_name
    ) g_alias
          ON g_alias.serv_id = r_alias.serv_id AND g_alias.node_id = r_alias.node_id

        LEFT JOIN p_cust_columns cust_cols
          ON cust_cols.node_id = r_alias.node_id AND cust_cols.serv_id = r_alias.serv_id

          where (((NOT (unreviewed_records IS NULL)) AND (unreviewed_records = 5)))

          group by r_alias.serv_id, r_alias.node_id
          order by g_alias.node_id ASC
          limit 25

CodePudding user response：

The reason aliases are not allowed in a WHERE clause is that the expressions in the SELECT list are not evaluated until after the rows are filtered by the WHERE clause. So it's a chicken-and-egg problem.

The easiest and most common alternative is a derived table:

SELECT a, b, c
FROM (
    SELECT a, b, a b AS c
    FROM mytable
    WHERE b = 1234
) AS t
WHERE c = 42;

This example shows that you can put some filtering conditions inside the derived table subquery, so you can at least reduce the result set partially, before the result of the subquery is turned into a temporary table.

Then in the outer query, you can reference a column that was derived from an expression in the select-list of the subquery. In this example, it's the c column.

The CTE approach is basically the same, it creates a temporary table to store the result of the inner query (the CTE), and then you can apply conditions to that in the outer query.

WITH t AS (
    SELECT a, b, a b AS c
    FROM mytable
    WHERE b = 1234
)
SELECT a, b, c
FROM t
WHERE c = 42;

The CTE solution is not better than the derived-table approach, unless you need to reference the CTE multiple times in the outer query, i.e. doing a self-join.

CodePudding user response：

Yeah, you are kind of SOL, WHERE can't know what an alias will be. So, frankly, a CTE, common table expression, is probably your best bet here. It should work, though not all RDBMS really support them (MySQL for example only in version 8).