Archives of the TeradataForum

Message Posted: Sun, 17 Oct 2004 @ 11:40:42 GMT

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

Prabhjot Sodhi wrote:

Your query doesn't deal with backups on database level (event.tablename NULL).

Try to replace NOT IN with NOT EXISTS and you'll never ever have problems with NULLs.

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

The coalesce(e.Tablename, t.Tablename) will filter all tables when events.Tablename is NULL for a backup on database level.

Dieter

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