Archives of the TeradataForum

Message Posted: Mon, 17 Dec 2007 @ 16:56:47 GMT

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

Suresh Koppusetty wrote:

          > Select databasename, tablename
          >     From dbc.TABLES
          >     Where databasename = 'db_a'
          >     Minus
          >     Select Databasename, tablename
          >     From dbc.TABLES
          >     Where databasename = 'db_b'

The easiest way is a Full Outer Join like this:

     Select
        coalesce(a.databasename, b.databasename),
        coalesce(a.tablename, b.tablename),
        case
          when a.tablename is null then 'table missing in database a'
          when b.tablename is null then 'table missing in database b'
          else 'table match'
        end
     from
     (select * from dbc.TABLES
       Where databasename = 'db_a')  as a
     full outer join
     (select * from dbc.TABLES
       Where databasename = 'db_b')  as b
     on
        a.databasename = b.databasename
     and
        a.tablename = b.tablename
     where
         a.tablename is null
     or b.tablename is null

This can be easily enhanced for missing columns, different datatypes, formats, defaults...

e.g

     case
        ....
        when a.ProtectionType <> b.ProtectionType then 'missmatch: protection
     types'
     end
     ...
     where
     ...
     or a.ProtectionType <> b.ProtectionType

Dieter

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