Archives of the TeradataForum

Message Posted: Mon, 17 Dec 2007 @ 16:52:54 GMT

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

Suresh,

You could FULL JOIN to accomplish the same. I have written this quick and dirty. Check if it works.

     SELECT
     T1.DatabaseName (TITLE 'Source 1')
     ,CASE WHEN T1.TableName IS NULL THEN TRIM(T2.TableName) || ' Does not
     exist in Source 1' ELSE T1.TableName END AS TableName_Src1
     ,T2.DatabaseName (TITLE 'Source 2')
     ,CASE WHEN T2.TableName IS NULL THEN TRIM(T1.TableName) || ' Does not
     exist in Source 2' ELSE T2.TableName END AS TableName_Src2
     FROM
     (SELECT
     DatabaseName
     ,TableName
     ,TableKind
     FROM DBC.Tables
     WHERE DatabaseName = 'DB1') AS T1
     FULL JOIN
     (SELECT
     DatabaseName
     ,TableName
     ,TableKind
     FROM DBC.Tables
     WHERE DatabaseName = 'DB2') AS T2
     ON T1.TableName = T2.TableName
     AND T1.TableKind = T2.TableKind;

Thanks,

Vinay Bagare

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