Selecting condition of a dual column (ORA-00904: Invalid identifier)

Question

I have a list with days and numbers generated by dual table as follow:

SELECT TO_CHAR (TO_DATE ('19-02-1984', 'DD-MM-RRRR') + LEVEL, 'DAY') DAY,
       LEVEL
  FROM DUAL
CONNECT BY LEVEL <= 7

And it's produces this table:

DAY     | LEVEL |
-----------------
MONDAY      1
TUESDAY     2
WEDNESDAY   3
THURSDAY    4
FRIDAY      5
SATURDAY    6
SUNDAY      7

Why can't do a select like WHERE WEEK_DAY = 'SATURDAY'?

SELECT TO_CHAR (TO_DATE ('19-02-1984', 'DD-MM-RRRR') + LEVEL, 'DAY') WEEK_DAY,
       LEVEL
  FROM DUAL
 WHERE WEEK_DAY = 'SATURDAY'
CONNECT BY LEVEL <= 7

It return the error message ORA-00904: Invalid identifier but i don't understand why.

Peter Peter · Accepted Answer · 2015-09-14T01:32:07

Basically, you can't refer to the WEEK_DAY alias from the SELECT clause in your WHERE clause because its value may not be known at the point at which the WHERE clause is evaluated. This is nothing unique to your query - it's just how it works.

You have a couple options ...

Option 1: reproduce the calculation from your SELECT clause in your WHERE clause:

SELECT TO_CHAR (TO_DATE ('19-02-1984', 'DD-MM-RRRR') + LEVEL, 'fmDAY') WEEK_DAY, LEVEL
  FROM DUAL
 WHERE TO_CHAR (TO_DATE ('19-02-1984', 'DD-MM-RRRR') + LEVEL, 'fmDAY') = 'SATURDAY'
CONNECT BY LEVEL <= 7

Option 2: move your query into an inline view and apply the WHERE filter to that:

select * from (
  SELECT TO_CHAR (TO_DATE ('19-02-1984', 'DD-MM-RRRR') + LEVEL, 'fmDAY') WEEK_DAY, LEVEL LVL
    FROM DUAL
  CONNECT BY LEVEL <= 7 )
 where WEEK_DAY = 'SATURDAY'

Note that I've also used fmDay in the to_char() function so there's no extra padding in the day name.

Selecting condition of a dual column (ORA-00904: Invalid identifier)

1 Answers