Archives of the TeradataForum

Message Posted: Thu, 10 Jul 2003 @ 12:11:21 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Thom,

try the following:

(1) can't remember off hand (I'll try and dig out some notes I've got).

(2a) show table x.y;   <= x = databasename, y = tablename
     OR help table x.y;
     OR Select * from dbc.tables where databasename='x' and tablename='y';
(2b) help volatile table x.y;
(2c) help index ????;  <= there are a number of variations on this, so
'roll your own'

(3) not easy. There is no REPLACE function (or equivalent) in Teradata sql. Up until Teradata 5.1 I've only seen this done on the client - i.e. not using sql - or with a combination of the Update command and substrings. With Teradata 5.1 this could probably be done with a UDF (user defined function).

(4) Again, there's no native sql function to do this, but...
   assume you wanted to LEFT justify a column 'c' in a 20-char field then
           cast(trim(leading from c) as char(20))
  assume you wanted to RIGHT justify column 'c' in a 20-char field then
           substring('       '||c from chars('       '||c)-19  for 20)
<== the '    ' is 20 spaces and '||' is the concatenation command within Teradata

(5) cast(date1 as char(10) format 'yyyy-mm-dd')
      cast(date1 as char(8) format 'yy/mm/dd')

Date-time columns have a data type of Timestamp. You can't use Format with these (well, I've never been able to), but the formatting is consistent based on the Timestamp definition, so a timestamp(0) column is always formatted as 'yyyy-mm-dd hh:mm:ss', this can be CAST as a char(19) which you can then substring to get the portion that you want.

Is that enough? Check the DDL manual for details of the commands.

Cheers,

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>