named subqueries in Teradata?


named subqueries in Teradata?

Hi ,

My company is moving one of our data warehouses to Teradata so I've been bumping into the differences between it and our previous rdms for that warehouse, Oracle.

I have a question that hopfully someone will be kind enough to address.

Does teradata allow named subqueries like:

with subquery1 as(select columns from a table),

subquery2(select columns from a different table)

select columns from subquery1 a, subquery2 b where a.column = b.column

And just out out curiosity, what table/view does sql assisstant use to fill information in the database explorer. In oracle I would use the dba_table or all_tables views.

Thanks in advance for your help.


Re: named subqueries in Teradata?

You can give names in Teradata for subqueries or columns in select clause, called as Alias Names.

Query syntax is something like this:

sel a.fld1,b.fld2 from

(sel * from tbl1) a,

tbl2 b

In Teradata you need to query on dbc.tables to get all the tables names and there database names.

If you need columns level details, you need to query on dbc.columns table. Here you can get information at data type level as well. This is useful when you don't have show or sel access on a particular table.


Re: named subqueries in Teradata?

With Derivedtablename As(
Sel ... from derivedtablename is a valid syntax on Teradata. Check out the SQL documentation for further details on the WITH statement.