Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Help with the NOT IN query...
 
From:   Dieter Noeth

Prabhjot Sodhi wrote:

  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:  



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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023