I would like to delete records when the difference between SYSDATE and a TIMESTAMP (6) field of my table is greater than 10 days. I have created the following query:
select (SYSDATE - myDate) as difference from myTable where difference > 10;
but i get the following error:
00904. 00000 - "%s: invalid identifier"
am i creating the query correctly?
Advertisement
Answer
am i creating the query correctly?
No, you cannot refer to an alias in the SELECT
clause in the filter condition of the same statement.
Additionally, when you subtract a TIMESTAMP
from a DATE
you will get the result as an INTERVAL
data type; so you need to compare on that rather than a NUMBER
(which would be the result if you compared DATE - DATE
).
You want:
SELECT SYSDATE - myDate as difference FROM myTable WHERE SYSDATE - myDate > INTERVAL '10' DAY;
db<>fiddle here