Common table expression - out of spool space

[DZanke]

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:

With cte_vars as

(

    Select 'SomeTable' Table1,

        'SomeOtherTable' Table2 

)

Select  tbl1.ColumnName From  DBC.Columns tbl1

Join cte_vars cte On tbl1.TableName = cte.Table1

Left Join DBC.Columns tbl2 On  tbl2.TableName = cte.Table2  And tbl2.DatabaseName = tbl1.DatabaseName

Where tbl1.ColumnName = tbl2.ColumnName And tbl1.tablename = cte.Table1 

And tbl2.ColumnName Is Null

Union

Select  tbl2.ColumnName From  DBC.Columns tbl2

Join cte_vars cte On tbl2.TableName = cte.Table2

Left Join DBC.Columns tbl1 On  tbl2.TableName = cte.Table1  And tbl2.DatabaseName = tbl1.DatabaseName

Where tbl1.ColumnName = tbl2.ColumnName And tbl2.tablename = cte.Table2 

And tbl1.ColumnName Is Null

Using volatile tables:

Create VOLATILE TABLE TblNames As

(         Select 'SomeTable' Table1,

        'SomeOtherTable' Table2 

 )

WITH DATA NO PRIMARY INDEX

ON COMMIT PRESERVE ROWS;

Select tbl1.ColumnName

From TblNames tn

Join DBC.Columns tbl1 On  tbl1.TableName = tn.Table1 

Left Join DBC.Columns tbl2 On  tbl2.TableName = tn.Table2  And tbl2.DatabaseName = tbl1.DatabaseName

Where tbl2.ColumnName Is Null

Union

Select tbl2.ColumnName

From TblNames tn

Join DBC.Columns tbl2 On  tbl2.TableName = tn.Table2

Left Join DBC.Columns tbl1 On  tbl1.TableName = tn.Table1 And tbl1.DatabaseName = tbl2.DatabaseName

Where tbl1.ColumnName Is Null

Is there some other way to do this? I am new to teradata, coming from SQL server environment.

[DZanke]

Whenever I replace the pseudo variables w/ literals, the queries run fine. I would like to eventually make this a more useful query.

[Fred]

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

Union

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

WHERE

And tbl1.ColumnName Is Null

[DZanke]

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.

[Fred]

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.