Archives of the TeradataForum

Message Posted: Mon, 14 Jun 2004 @ 15:40:47 GMT

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

-----------------------
Admin Comment: The attachment for this post can be found in the Attachment area of the TeradataForum website:

www.teradataforum.com/attach.htm

-----------------------

Hi,

I am having a strange problem in Teradata. While populating one of the dimension table, few record's texts are getting truncated after 50 characters even if the column size is VARCHAR(100). In the source table, data is being loaded from a text file using fastload, there it is loaded correctly. But during ETL, when the final dimension table is loaded from this source table using BTEQ script, few records (out of 2000, only 18 records) get truncated after 50 characters. The two table's structure and the SQL used to load the dimension table are:

Source:

     CREATE SET TABLE DB1.EXCEL_ANSREF
     (OriginalQuestionID VARCHAR(30) NOT NULL,
      QuestionID INTEGER NOT NULL,
      ExistingAnswerID INTEGER NOT NULL,
      ExistingAnswerDS VARCHAR(200),
      NewAnswerID INTEGER NOT NULL,
      NewAnswerDS VARCHAR(100)
     )UNIQUE PRIMARY INDEX ( OriginalQuestionID ,ExistingAnswerID );

Target:

     CREATE SET TABLE DB2.LU_ANSWER_DETAIL
     (     QuestionID INTEGER NOT NULL,
           AnswerDtlID INTEGER NOT NULL,
           AnswerID INTEGER NOT NULL,
           AnswerDS VARCHAR(100) NOT NULL,
           AnswerDtlDS VARCHAR(100)

     )PRIMARY INDEX ( QuestionID ,AnswerDtlID );

SQL:

     INSERT INTO DB2.LU_ANSWER_DETAIL
     (QuestionID,AnswerDtlID,AnswerID,AnswerDS,AnswerDtlDS)
     SELECT DISTINCT
     qst.QuestionID,ans.ExistingAnswerID,ans.NewAnswerID,ans.NewAnswerDS,ans.
     ExistingAnswerDS
     FROM  EXCEL_ANSREF ans

     INNER JOIN EXCEL_QSTREF qst ON
          qst.OriginalQuestionID = ans.OriginalQuestionID;

ETL process is run once in a quarter. Strange thing is that during ETL run, data in "AnswerDS" column got truncated only for 18 records (both in QA & Production box) but when I created a new table and tried to populated that using the same SQL, it got loaded correctly this time. What could be the reason behind this strange behavior. Please note that "AnswerDtlDS" column is also VARCHAR(100) length field and it is having text longer than 'AnswerDS' field and in this column data is getting loaded correctly every time.

For reference I am attaching data for these 18 records. Please help.

Thanks

Rishi

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