I am trying to write a query which will ID columns in one table not in another table w/o having to use literals. I've tried variations using both CTEs and volatile tables, but run out of spool:
Is there some other way to do this? I am new to teradata, coming from SQL server environment.
Whenever I replace the pseudo variables w/ literals, the queries run fine. I would like to eventually make this a more useful query.
Main issue is that you need to include the column name in the join conditions.
You should be using ColumnsV instead of legacy view Columns. And I think you really should supply the DB/Table for each object being compared.
With cte_vars as
Select 'SomeDB' DB1, 'SomeTable' Table1,
'OtherDB' DB2, 'SomeOtherTable' Table2
Select tbl1.ColumnName From DBC.ColumnsV tbl1
Join cte_vars cte On tbl1.TableName = cte.Table1 and tbl1.DatabaseName = cte.DB1
Left Join DBC.ColumnsV tbl2 On tbl2.TableName = cte.Table2 And tbl2.DatabaseName = cte.DB2 And tbl1.ColumnName = tbl2.ColumnName
WHERE tbl2.ColumnName Is Null
Select tbl2.ColumnName From DBC.ColumnsV tbl2
Join cte_vars cte On tbl2.TableName = cte.Table2 and tbl2.DatabaseName = cte.DB2
Left Join DBC.ColumnsV tbl1 On tbl1.TableName = cte.Table1 And and tbl1.DatabaseName = cte.DB1 And tbl1.ColumnName = tbl2.ColumnName
And tbl1.ColumnName Is Null
Wow, just changing the query to use ColumnsV instead of columns solved the problem! But I will heed your advice re: suppling the DB names as well. So views that end w/ 'V' are more current than ones that don't? Are views that end w/ 'VX' more current than ones that end w/ just 'V'? I am trying to come up w/ some rudimentary tools to compare tables, identify indices,etc.
Since TD12, the V views are the preferred / "native" ones, while those without V (where one with V also exists) were rewritten to provide backward compatibility.
The X views are filtered / restricted to show only objects for which the requester has some access. There is a performance cost for this filtering, so you should use the X views only if required; and if you do use the X views, it becomes more important to collect statistics on the dictionary tables themselves.