SQLite - no such column: X-CodePudding

I know that problem lies in SELECT being executed last. When CASE is executed, column X does not exist yet. Columns X and its values appear from subtraction of columns a and b values => ROUND((a-b)),2) AS [X] You can notice it in the full code lower: Basically the question is the following: how do I make column X appear with its values before CASE.

SELECT 
    e, a, b,ROUND((a-b)),2) AS [X],
    CASE WHEN X BETWEEN 5 AND 10 THEN 'Good Choice'
    ELSE 'bad choice' END AS 'choice'
FROM table_1
LEFT JOIN table_2 ON table_1.column_1 = table_2.column_2
Where BBB = "VVV" OR BBB = "CCC"
GROUP BY column_1;

CodePudding user response：

You can't refer to the alias X inside the same select in which it was defined. Just repeat the round expression in this case:

SELECT e, a, b, ROUND(a - b, 2) AS X,
       CASE WHEN ROUND(a - b, 2) BETWEEN 5 AND 10
            THEN 'Good Choice' ELSE 'bad choice' END AS choice
FROM table1 t1
LEFT JOIN table2 t2 ON t1.column1 = t2.column2
WHERE BBB = 'VVV' OR BBB = 'CCC'
GROUP BY column1;