CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user2428207
user2428207

Reputation: 825

Compare two Timestamps from different Tables

I would like to compare to Timestamps from different tables. I want to get all rows where the stamp from tbl_1 is bigger than the MAX(stamp) from tbl_2.

select *
from tbl_1
where timestamp > (select MAX(TIMESTAMP) from tbl_2);

unfortunatelly this is not working. I'm always getting zero results :/. Datatype of both colums is date/time

OK I found the problem.

The Problem is that if tbl_2 is empty and has no dates, it wont return anything. It works though when I enter a smaller date.

Upvotes: 0

Views: 1808

Answers (2)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Answering the question from the author's answer...

You can use NVL to take into account the possibility of the subquery returning NULL (when tbl_2 is empty):

select *
  from tbl_1
where timestamp > NVL((select MAX(TIMESTAMP) from tbl_2), timestamp - 1);

Upvotes: 1

John Wesley Gordon
John Wesley Gordon

Reputation: 910

I find doing a manual comparison helps. Run your query

select MAX(TIMESTAMP) from tbl_2

and see if the date in it is a date bigger than any date you have in tbl_1

select MAX(TIMESTAMP) from tbl_1

If so that explains why you aren't getting an results and you will have to look at those large timestamps to determine if the records need changed.

To get this to work to return all rows even when you are returning NULL for the MAX(TIMESTAMP) query in tbl_2

select *
from tbl_1
where timestamp > (select MAX(TIMESTAMP) from tbl_2) or (select MAX(TIMESTAMP) from tbl_2) is null;

Upvotes: 1

Related Questions