Char40 to Char10

ds_is_fun · Post by **ds_is_fun** » Fri Sep 28, 2007 7:50 am

Im moving data from source type CHAR40 To target type CHAR10.
When I load the data into SQLSERVER im getting an Warning message -
Ref_T63GS02_Load_S_T_US_v6..Transformer_2.DSLink4: DSD.BCIPut call to SQLExecute returned informational message.
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated

Job runs successfully but for every row there is the above message in Director.

chulett · Post by **chulett** » Fri Sep 28, 2007 7:57 am

Stop letting the database truncate the data for you, do it yourself in the job. Substring off only the first 10 characters and move that into the smaller field.

ds_developer · Post by **ds_developer** » Fri Sep 28, 2007 8:03 am

Are you trimming the data? If a varchar 40 is loaded (in your source) with trailing spaces, the spaces will remain. Usually you want to trim those off. Even after that, you should only use the first 10 characters in your target field. The syntax for that is [1,10] on the output derivation of the incoming field.

John

ray.wurlod · Post by **ray.wurlod** » Fri Sep 28, 2007 3:48 pm

If you do want the database to perform the truncation, specify it as CHAR(10) and extract it using the SUBSTRING function in SQL.

Otherwise extract as Char(40) and use an appropriate function (such as Left()) or a substring operator to obtain the leftmost 10 characters.

In all of this, although you did not specify, I have assumed you simply want to discard the remaining 30 characters. For you simply can not shoehorn 40 characters into a Char(10) field.