Oracle SQL Previous Month query issue

Question

So far I've got the following:

SELECT TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') as "Depart_Month",
       TO_CHAR(sysdate, 'mm')-1 as "Current_Month"
  FROM "HOL_DEPART_DATES" "HOL_DEPART_DATES"
 WHERE "Depart_Month" = "Current_Month"

However this gives me an error:

ORA-00904: "Current_Month": invalid identifier

However without the WHERE clause, it works fine. Any ideas?

Answer 1

Unfortunately you cannot reference the column aliases in the WHERE clause as they are not yet available. You can either do this:

select  TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') as "Depart_Month",
          TO_CHAR(sysdate, 'mm')-1 as "Current_Month"
from     "HOL_DEPART_DATES" "HOL_DEPART_DATES"
where     TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') = TO_CHAR(sysdate, 'mm')-1

or do this:

select "Depart_Month", "Current_Month"
from
( select  TO_CHAR("HOL_DEPART_DATES"."DEPART_DATE", 'MM') as "Depart_Month",
          TO_CHAR(sysdate, 'mm')-1 as "Current_Month"
  from     "HOL_DEPART_DATES" "HOL_DEPART_DATES"
)
where     "Depart_Month" = "Current_Month"