Archives of the TeradataForum

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

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

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.

Thanks & Regards,

Pete

	<Prev	Next>		<<First	<Prev	Next>	Last>>