help with concatenate operator in select query

[bcdixit]

I am trying to run the following query

Select 'drop table'||distinct(TableName)||';'
From dbc.indices
Where DatabaseName = 'STG_x'

The query spits out a syntax error: expected something between "'||'" and the 'distinct' keyword.

Basically I want to list all the distinct TableNames with
'drop table' before the table name and ';' after the table name.

so I want

NAME
--------------
drop table a;
drop table b;
drop table c;
..
..
..

can anybody suggest where I am going wrong in the query?
I am using teradata rdbms.

Thanks
bcd

 

[Zathras]

Try it this way
[tt]
Select distinct 'drop table '|| TableName ||';'
From dbc.indices
Where DatabaseName = 'STG_x'
[/tt]
(Don't forget the space between "table" and the table name.)

 

[bcdixit]

Thanks.

 

[dnoeth]

Some additional info, because this is a Teradata question...

If you use dbc.tables instead of dbc.indices then there's no need for a distinct.
And better fully qualify the tablename:

Select
'drop table ' || trim(databasename) || '.'
|| trim(TableName) ||';'
From dbc.tables
Where DatabaseName = 'STG_x'
and tablekind = 't';

And if you want to drop all objects (including views, macros...) there's a "delete database stg_x;".

Btw, there's a forum for Teradata, too:

http://www.tek-tips.com/threadminder.cfm?pid=328

And there's

http://www.teradataforum.com

Dieter

 

[bcdixit]

Thanks Dieter