Archives of the TeradataForum

Message Posted: Sun, 17 Oct 2004 @ 09:53:40 GMT

I am trying to retrieve the list of tables that have not been backed up in the last backup job. So, I submitted the following query:

Sel Databasename,
tablename,
case TableKind
when 'T' then 'TABLE'
when 'V' then 'VIEW'
when 'M' then 'MACRO'
else TableKind
end
from dbc.tables
where (Databasename, Tablename) NOT IN
(
Sel Databasename, Tablename
from dbc.events
Where eventType = 'Dump'
and CreateDate > '2004/10/03'
)
and CreateTimeStamp < '2004/10/03 00:00:00'
order by databasename, tablename;

The result came with only a couple of rows. Though it made the team happy!!!! But later we found that some more tables were missed out.
On browsing the NOT IN documentation it states, that if the NOT IN query has some NULLS in the inner query, the result set is erroneous.

The query once modified as below, gave better results, but does not inform me of tables or databasenames that are NULL.

Sel Databasename,
tablename,
case TableKind
when 'T' then 'TABLE'
when 'V' then 'VIEW'
when 'M' then 'MACRO'
else TableKind
end
from dbc.tables
where (Databasename, Tablename) NOT IN
(
Sel Databasename, Tablename
from dbc.events
Where eventType = 'Dump'
and CreateDate > '2004/10/03'
and Databasename is not NULL
and Tablename is Not NULL
)
and CreateTimeStamp < '2004/10/03 00:00:00'
order by databasename, tablename;

Does any one have any information to work arround the NOT IN clause for NULL able inner query, using NOT IN caluse.