special character oddity

[travissolt]

In my transformation I am hard coding a ORIGINAL_DATA_SOURCE and then sending the rows into the final staging dimension table through an insert or update. The initial insert works fine but I noticed the ORIGINAL_DATA_SOURCE included three spaces not established in the transformation. When testing updates it rejects all update cases and sends the bad rows to an error table. In this table I see the ORIGINAL_DATA_SOURCE with three special character (boxes) after the ORIGINAL_DATA_SOURCE. I have tried the below derivations. TRIM('DMD' : TRIM(pEnv)) where pEnv is the environment variable in this case it was DMED. TRIM('DMD' : pEnv) TRIM ('DMD' : SpaceWhiteSpace(pEnv)) I set up a variable for DMD and tried pDataSrc : pEnv. I tried hardcoding it to 'DMDDMED' etc... The only way i got it to work is if i used up all the characters in the field 'DMDDMEDDDD' so that the spaces disappeared. All the other fields in the transformer came into the table with no trailing spaces except the one I am discussing. Any thoughts?

[kcbland]

What's the datatype of the target column?

[Andet]

Sounds like you're moving less than the number of characters defined into a character field. If a character column is say, 10 characters, and you move 5 characters into that field, what would you expect to be in the rest of the field? Probably your default fill character, usually a null '\0' or blank.
Change the data type to varchar, and your problem should go away.

[travissolt]

Didn't even think it could be a data type issue but it is a Char so thanks. i will change it to varchar2 and give it a go.

[kcbland]

Didn't even think it could be a data type issue but it is a Char so thanks. i will change it to varchar2 and give it a go.

That's why I asked the datatype. Arrrgghh! CHAR strikes again!!

[travissolt]

Argghhhhhhhh is right I should have checked that first thing. Thanks for the help that fixed my problem.