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?

Aleksej · Accepted Answer · 2017-05-24 14:54:06Z

1

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>

answered May 24, 2017 at 14:54

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user5683823 Over a year ago

@BreenDeen - To further explain Aleksej's answer: It always help to think about the order of evaluation of clauses in a SQL statement. At least in principle, the FROM clause (including JOIN conditions) and the WHERE clause are evaluated before anything else, while the SELECT clause is evaluated close to the end. The aliases defined in the SELECT clause are not visible to the WHERE clause. Database software creators are free to do other things (such as "read ahead" for any aliases defined later), but I don't know if any do it for this specific operation, and certainly Oracle does not.

user5683823 Over a year ago

@BreenDeen - As an alternative to the use of subqueries, you could repeat the whole TO_CHAR(DT.OP_DATES.LST_UPDT_TMSP,'DD-MM-YY') on the left-hand side of the WHERE clause, instead of using the alias. This would avoid the subquery-outer query structure. It is more typing, but it is NOT inefficient; the function appears twice in the query, but it is not actually executed twice. The Oracle engine is smart enough to see it's the same function, so it is computed only once (for each row).

SandPiper · Accepted Answer · 2017-05-24 16:29:54Z

0

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)

answered May 24, 2017 at 16:29

SandPiper

2,9145 gold badges38 silver badges59 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Select

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related