Error using "select round" in WHERE filter

Question

I need to do a "WHERE" filter in my code, like that:

    "select
    round ((aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento),7)*1440 as HF

    from
    qt_qts.res_tubo_austenitizacao aust

   WHERE
   HF between 'time1' and 'time2' <----------------- THE ERROR
   ")

But it show that HF is not recognized in my code. So, i tried to put the full name (aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento) and it worked... BUT, i need to put the full expression, what converts the time to seconds (((aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento),7)*1440). That way, an error appear, cause of the expression.

I'm using ORACLE database

How can I refeer to the HF time with no error in my code?

Possible duplicate of [SQL not recognizing column alias in where clause](https://stackoverflow.com/questions/28802134/sql-not-recognizing-column-alias-in-where-clause) and [many other similar questions](https://stackoverflow.com/questions/28802134/sql-not-recognizing-column-alias-in-where-clause). — IMSoP, Feb 21 '19 at 14:08

score 0 · Accepted Answer · answered Feb 21 '19 at 14:07

Aliases defined in a given SELECT clause are not available in the WHERE clause at the same level, because the latter is executed before the former. You have two choices here: you may either repeat the expression to which you assigned the HF alias, or you can subquery. Here is the first option:

SELECT
    ROUND((aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento),7)*1440 AS HF
FROM qt_qts.res_tubo_austenitizacao aust
WHERE
    ROUND((aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento),7)*1440
        BETWEEN 'time1' AND 'time2';

And here is the subquery option:

WITH cte AS (
    SELECT
        ROUND((aust.DTH_DESENFORNAMENTO - aust.dth_enfornamento),7)*1440 AS HF
    FROM qt_qts.res_tubo_austenitizacao aust
)

SELECT HF
FROM cte
WHERE HF BETWEEN 'time1' AND 'time2';

It worked! Thanks. I prefer the first option to clean the code. Really thanks. — Daniel Santos, Feb 21 '19 at 14:30

Error using "select round" in WHERE filter

1 Answers1