Getting the column type/length/format of a view

[ollibaer]

Hi,

i want to retrieve the definition (length/format/type etc.) of the columns used in a view.

Is this query possible with the metadata? Does any table in DBC contain these information? Or do i need to (recursivly) parse the view definiton (the select statment) manually?

Queryman shows me the requested informations? Magic, hard work or just a clever Metadata-join?

Thanx for your help!
Olli

 

[dnoeth]

You mean Tools -> List Columns in Queryman?

It's a special kind of voodoo:
HELP COLUMN myview.*;
instead of
HELP VIEW myview;

Dieter

 

[tdatgod]

you can also simply select the information you want.

sel distict type(column) from table;
sel distinct format(coulumn) from table;

don't forget the distinct or you will get one row returned for every row in your table.

 

[dnoeth]

Nooooooo, don't do it this way, this will do the select and then all the rows will be redistributed to a single AMP before the distinct. This will result in a nicely skewed spool. Just try it.
If you really want to use those attribute function use a GROUP BY instead:
sel type(column) from table group by 1;
It's still doing the select, but the group by is much more efficient in this case.

Btw, if you use
sel type(db.table.column);
it's just a
-> The row is sent directly back to the user as the result of
statement 1.

Dieter

 

[tdatgod]

Hi,
Yes I asked our ANSI SQL experts and that is what they told me.

select type(table.column);

is returned directly by the parser but is a TERADATA EXTENSION to the SQL standard. Now other database vendors may support this but it isn't ANSI.

I always get distint and group by backwards. is this correct?

This when number of values are small verus number of rows.

sel type(column) from table group by 1;

This when the number of values is Large versus the number of rows.

sel distinct (column) from table;

 

[dnoeth]

TYPE is non-ANSI, no matter how you use it.
The standard compliant way would be a select from INFORMATION_SCHEMA.COLUMNS, but those views are not implemented in Teradata...

Regarding "group vs. distinct": you got it right ;-)

Dieter