CODEWITHSUNDEEP
sqloracle-databasedatetimesubqueryintervals
Maiky
Maiky

Reputation: 57

SQL interval with subqueries

I want filter records oldes for some date and this SQL is not working:

SELECT * 
FROM TABLE1 
WHERE UPDATED_ON + INTERVAL (SELECT B.DAYS FROM B.TABLE2 
                             WHERE B.NAME = 'Tmp') DAY < SYSDATE;

But this SQL is working because have constant 2 in ''. How I can do this with subqueries instead constant?

SELECT * 
FROM TABLE1 
WHERE UPDATED_ON + INTERVAL '2' DAY < SYSDATE;

Upvotes: 1

Views: 530

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Either use

WHERE
   UPDATED_ON 
      + (SELECT B.DAYS FROM B.TABLE2 WHERE B.NAME = 'Tmp') * INTERVAL '1' DAY < SYSTIMESTAMP

or

WHERE
   UPDATED_ON 
      + NUMTODSINTERVAL((SELECT B.DAYS FROM B.TABLE2 WHERE B.NAME = 'Tmp'), 'day') < SYSTIMESTAMP

or

WHERE
   UPDATED_ON 
      + (SELECT B.DAYS FROM B.TABLE2 WHERE B.NAME = 'Tmp') < SYSDATE

Upvotes: 3

GMB
GMB

Reputation: 222462

You could write this as:

select t1.*
from table1 t1
where updated_on < (
    select systimestamp - t2.days * interval '1' day from table2 t2 where t2.name = 'Tmp'
)

Alternatively, if updated_on is a daterather than a timestamp:

select t1.*
from table1 t1
where updated_on < (
    select sysdate - t2.days from table2 t2 where t2.name = 'Tmp'
)

Note that this will fail if there is more than one row in table2 whose name is 'Tmp'.

Upvotes: 1

VBoka
VBoka

Reputation: 9083

You can try it like this:

select UPDATED_ON
       , UPDATED_ON  +  (SELECT t2.DAYS FROM TABLE2 t2 WHERE t2.NAME = 'Tmp') 
FROM TABLE1 
WHERE UPDATED_ON +  (SELECT t2.DAYS FROM TABLE2 t2 WHERE t2.NAME = 'Tmp')  < SYSDATE;

Here is a demo:

DEMO

Upvotes: 1

Related Questions