All Forums Database
kbat 1 post Joined 09/11
01 Sep 2011
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.

 

 

rads 17 posts Joined 07/08
18 Sep 2011

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.

AbeK 24 posts Joined 08/09
28 Sep 2011

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

You must sign in to leave a comment.