Archives of the TeradataForum

Message Posted: Fri, 20 Jul 2007 @ 15:09:30 GMT

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

While running a query to check-up on the internal usage of standard naming, I elected to use some SET processing and got a good return using the (superior) Teradata extension: MINUS. When I tried the query with the ANSI standard of EXCEPT the query failed.

It looks like the extended logic in the select statement is behind the failure. I'd prefer using the ANSI standard syntax - if at all possible

Any thoughts on how to make this work with ANSI standard syntax?

Note: at this time I am not prepared to use Mr. Martinez's standing recommendation: Sacrifice a goat on the keyboard, then run the query.

SQL in question:

     select databasename ,  substring( trim(tablename) , 1 , (index
     (tablename,  '_xxx' )   - 1  )  )  as tblnm
      , columnname , indexnumber
     from dbc.indices
     where databasename = 'zzztd756'
     and indexnumber = 1
     and trim(tablename) like '%$old_xxx'

     MINUS       /** NOTE when EXCEPT fails, the extremely useful 3706 error
     message is returned ***/

     select databasename, trim(tablename)  as tblnm
     , columnname, indexnumber
     from dbc.indices
     where databasename = 'zzztd756'
     and indexnumber = 1
     and trim(tablename) like '%$old'

     order by 1, 2, 3

Thanks,

Bill Gregg
Teradata Team
Thomson Healthcare

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