Archives of the TeradataForum

Message Posted: Tue, 14 Feb 2006 @ 15:26:54 GMT

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

Thanks for everyone's help. With Fred's clarification that there is no such thing as "no format" but rather this condition is really the "default format", I wrote this SQL. It is pretty much untested at this point, but wanted to close this out in case others were hanging on the edge of their desks awaiting resolution.

The issue here was only with Decimal and Integer. You could expand upon this SQL for other data types if necessary.

     select
     'ALTER TABLE '|| TRIM(databasename)||'.'||TRIM(tablename)
     ||' ADD '|| TRIM(columnname) ||' FORMAT '''||
     CASE WHEN columntype = 'I' then '-(10)9'''
          WHEN columntype = 'D' then '--('||
     trim(decimaltotaldigits-decimalfractionaldigits)
     || ')'||
     CASE WHEN decimalfractionaldigits > 0 THEN
     '.9('|| trim(decimalfractionaldigits)||')'';'
          ELSE ''';'
     END
     END

      from dbc.columns

     where databasename = 'ODS_A_DEV'
     and columntype in ('I','D');

Thanks again for your help.

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