Filter by a calculated colum in SQL Oracle

Question

I am trying to SELECT columns for which the difference in days exceeds 50.

However I am unable to do so. Find below my query:

SELECT name_,
       client_p as Client,
       to_date(first_date)-to_date(last_day) as difference
FROM   table1.mydata
where  difference>50

any insights on this?

Thank you.

score 0 · Answer 1 · answered Jun 10 '22 at 10:13

You can't reference it that way; either use this

SELECT name_,
       client_p AS client,
       TO_DATE (first_date) - TO_DATE (LAST_DAY) AS difference
  FROM table1.mydata
 WHERE TO_DATE (first_date) - TO_DATE (LAST_DAY) > 50;

or - with your query as a CTE (or a subquery):

WITH
   temp
   AS
      (SELECT name_,
              client_p AS client,
              TO_DATE (first_date) - TO_DATE (LAST_DAY) AS difference
         FROM table1.mydata)
SELECT *
  FROM temp
 WHERE difference > 50;

Note that you should store dates as DATEs, not strings - which is what TO_DATE function suggests.

But, if you do store them as strings, then you should provide format model to the TO_DATE function. It is unknown which format you do have, but - you should know it.

On the other hand, if you store dates as you should (in DATE datatype columns), then remove TO_DATE from your query.

score 0 · Answer 2 · answered Jun 10 '22 at 10:13

You can't use an alias to filter right away. Either make a subquery and select from that using the alias or change to:

SELECT name_,
       client_p as Client,
       to_date(first_date)-to_date(last_day) as difference
FROM   table1.mydata
where  to_date(first_date)-to_date(last_day) > 50

Filter by a calculated colum in SQL Oracle

2 Answers2