Archives of the TeradataForum

Message Posted: Wed, 09 Nov 2005 @ 18:45:13 GMT

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

I determined how MSTR is getting column lengths (and thus how I'm guessing Queryman / SQL Assistant is doing it as well). We set up a seperate environment and turned SQL tracing on to catch the SQL that MSTR is passing to perform this.

Basically it's doing:

     help column ..

for each column in the view it's trying to update.

However, this is causing us a problem as the MaxLength returned in this is twice as large as the Format says it should be.

The DDL for the view is:

     REPLACE VIEW ViewName
     (Column1, Column2a, Column2b)
     AS LOCK ROW FOR ACCESS
     SELECT
     Column1,
     CASE WHEN Column2 IS NULL then '' ELSE Column2 END Column2a,
     COELESCE (Column2, '') Column2b
     FROM TableName;

The DDL for the table is:

     CREATE TABLE TableName (Column1 char(10) NOT NULL, Column2 char(10))
     UNIQUE
     PRIMARY INDEX (Column1);

"help column DatabaseName.TableName.Column2a" returns a MaxLength of 20 and a Format of X(10).

"help column DatabaseName.TableName.Column2a" returns a MaxLength of 10 and a Format of X(10). (The expected result)

Any ideas of why this is going on? Correcting this would involve correcting a couple thousand columns in views, so we aren't all the thrilled about going that route...

Mike

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