Oracle SQL Select

Question

I wrote a simple query to compare dates but I am getting an invalid identifier error on the last line of the query. I get the following error below. Line 15 happens to be the last line of the query.

Error at Line: 15 Column: 6

 Select OP_DATE,
        ID,
        TO_CHAR(DT.OP_DATES.LST_UPDT_TMSP,'DD-MM-YY') as SD 
   From DT.OP_DATES
  Where OP_DATE_IND = 'CMPLTD'
    And OP_DATE_STS = 'M'
    And SD=(SELECT TO_CHAR(SYSDATE, 'DD-MM-YY') FROM DUAL)

What's wrong with the identifier?

Answer 1

To elaborate on the accepted answer:

 Select OP_DATE,
        ID,
        TO_CHAR(DT.OP_DATES.LST_UPDT_TMSP,'DD-MM-YY') as SD 
   From DT.OP_DATES
  Where OP_DATE_IND = 'CMPLTD'
    And OP_DATE_STS = 'M'
    And TO_CHAR(DT.OP_DATES.LST_UPDT_TMSP,'DD-MM-YY') = 
            (SELECT TO_CHAR(SYSDATE, 'DD-MM-YY') FROM DUAL)

Answer 2

You can not use the alias SD in the where condition. For example:

SQL> select 1 as one
  2  from dual
  3  where one = ( select 1 from dual) ;
where one = ( select 1 from dual)
      *
ERROR at line 3:
ORA-00904: "ONE": invalid identifier


SQL> select 1 as one
  2  from dual
  3  where 1 = ( select 1 from dual) ;

       ONE
----------
         1

If you want to use the alias in the where condition, you need to wrap your query to get a result containing a column with the needed alias:

SQL> select *
  2  from (
  3          select 1 as one
  4          from dual
  5       )
  6  where one = ( select 1 from dual) ;

       ONE
----------
         1

SQL>