Re: finding dates are not in table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/07/22
Message-ID: <33d53d40.23906926@newshost>#1/1

On 22 Jul 1997 23:22:50 GMT, "Sandra Aguilar Velasco" <saguila_at_tlaloc.imta.mx> wrote:

>I am trayin with sqlplus find all the dates are not in my table.
>
>How I can do it? ...
>besides create another table and put in this all the dates with out blanks.

All of the dates? Are you sure, that would be alot of them from 4712BC to 4712AD :)

Ok, assuming you meant, all of the dates in this YEAR or in some range, you can try something similar to the following. It shows you all of the days you didn't create a new user account in your database. It relies on the fact that there are more then 365 objects in all_objects by default.

select *
from (select username, trunc(created) created

        from all_users ) all_users,
     ( select to_date(rownum,'ddd') aday
         from all_objects
        where rownum <= 365 ) thedays

where aday = created(+)
and created is null
/

The second virtual table, theDays, is really a table of all of the days in the current year (change 365 to 366 for leap years). I outer join this table to all_users (can't outer join directly on trunc(created) so we create another virtual table for that, and only keep the rows where we didn't find a match in all_users...

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jul 22 1997 - 00:00:00 CDT