Using column aliases in a WHERE clause

SQL & Databases / By

Just wanted to answer a question on StackOverflow, where someone had a data model like this: 

CREATE TABLE tvalues (cid NUMBER, cvalue NUMBER);
INSERT INTO tvalues VALUES(1, 200);
INSERT INTO tvalues VALUES(2, 1500);
INSERT INTO tvalues VALUES(3, 3200);
INSERT INTO tvalues VALUES(4, 4500);
COMMIT;
SELECT * FROM tvalues;

CID  CVALUE
------------
  1    200
  2   1500
  3   3200
  4   4500

The question was how to get the cid of the row so that a given value is between the value of the previous row and the value of the row for which to return the id (e.g. given value = 4000 should return id=4). Obviously, since in a relational database system, rows do not have an inherent order (there is no “next” or “previous” row), we need a sort criteria which I simply assumed to be the CID column. The solution I had in mind was to use an analytical function like LEAD() or LAG(). These functions allow to access the next or the previous row (see how the analytical function requires an ORDER definition, otherwise it would not know what the next or previous row is): 

SELECT cid, LAG(cvalue, 1, 0) OVER(ORDER BY cid) cmin, cvalue cmax
FROM tvalues;

CID     CMIN        CMAX
--------------------------
  1        0         200
  2      200        1500
  3     1500        3200
  4     3200        4500

This returns a nice range-wise view of the data. Now the final solution should be easy: simply restrict the result set so that the given value is between the minimum and the maximum value: 

SELECT cid, LAG(cvalue, 1, 0) OVER(ORDER BY cid) cmin, cvalue cmax
FROM tvalues
WHERE cmin < 4000 AND cmax > 4000;

However, this query returns an error message: 

ORA-00904: "CMAX": invalid identifier

The reason is that it is generally not possible to use column aliases in the WHERE clause (or in any other part of the SELECT statement, with a few exceptions like ORDER BY). This also has nothing to do with the analytical functions – the following simple query does not work either: 

SELECT cvalue avalue
FROM tvalues
WHERE avalue > 1000;

ORA-00904: "AVALUE": invalid identifier
00904. 00000 -  "%s: invalid identifier"

The reason is that the select list (the projection) is the last part which is processed in an SQL statement, hence the SQL parser does not yet know the column aliases when it processes other parts of the query, like the WHERE statement (btw, while investigating this, someone else posted a very similar answer to the original question). The final solution is easy: the query just needs to be wrapped into another query as a sub query: 

SELECT * FROM (
   SELECT cid, LAG(cvalue, 1, 0) OVER(ORDER BY cid) cmin, cvalue cmax
   FROM tvalues
) WHERE cmin < 4000 AND cmax > 4000;

CID     CMIN        CMAX
--------------------------
4	3200	    4500