How to use column alias define in select query in where clause

Question

Need to use the alias refrence, define for column in where clause.

SELECT COUNT(S.ID)
     , SUBSTR(D.DEPARTENT_ID, 42,INSTR(D.DEPARTENT_ID, '.',2)-8) AS "**DEPT**"
  FROM STUDENT S, DEPARTMENT D
 WHERE S.OBJ LIKE '%' || **DEPT** || '%'

Answer 1

Have a derived table for the SUBSTR part.

Switch to modern, explicit JOIN syntax.

Add GROUP BY.

SELECT COUNT(S.ID), DEPT
FROM STUDENT S
    JOIN (SELECT SUBSTR(D.DEPARTENT_ID, 42,INSTR(D.DEPARTENT_ID, '.',2)-8) AS DEPT
          FROM DEPARTMENT) D
    ON S.OBJ LIKE '%' || DEPT || '%'

GROUP BY DEPT

Answer 2

This won't work since the SELECT statement will be loaded after the WHERE clause.

The order of operations for your query is: FROM -> WHERE -> Aggregations (like the one you used). You would need to put the aggregation in the where clause as well.