Integer to Char conversion questions

[srini.dw]

Hi,

Please need your help in converting Integer to Char function.

I have the below values as Integer in the source.

123456
654321

I have done a direct map across Integer to Char in Transformer.

I am getting the below values

123456??
654321??

In the target table (Oracle) its defined as Char(8)

As searched the forum, it says no need of any conversation from Integer to string.

Any thoughts.

Thanks,

[ArndW]

Could it be that you have converted to a CHAR(8)? Did you do an explicit or an implicit conversion in your transform stage?

[srini.dw]

Thanks for the reply.

The column is being directly mapped in transformer from
Col_1(Integer) to Col_1(Char(8)) its eight

Thanks,

[crystal_pup]

Can you try using "APT_STRING_PADCHAR" in the job and set its value to 0x20

[srini.dw]

Thanks,

This job already has a variable APT_STRING_PADCHAR with value 0x20.

But when I run the job by making
CHAR(8) to varCHAR(8)

jobs runs fine.

Is it because of length giving this issue?

Thanks,

[crystal_pup]

hmmm i tried exactly what you have coded and in my target table (created on Netezza), I am not seeing any "??" suffixed in the data.Also, i didn't make use of APT_STRING_PADCHAR in my job.

[srinivas.g]

Yes. Due to Length issue. By the way what is your character set in Target oracle??

[asorrell]

Yes - the problem is the CHAR setting. Remember - CHAR fields are fixed length and the one you are converting to is bigger than your integer numbers, so it is padding. It is padding on the right, not the left (usually bad for a number) and looks like it is using a default $APT_STRING_PADCHAR of 0x00 (worse). Note that the padding might have occurred in a previous job if it did a poor job of handling the fixed length CHAR fields. Check the data out in a hex editor to see what the ?? characters really are.

The default of 0x00 (Hex 0) is really a nasty semi-invisible character. The recommendation was to change it to 0x20, which is a space. However, that won't fix the fact it is probably padding on the wrong side of the number.

One solution is to covert them to a VARCHAR stage variable (which will not pad the string). Then pad the VARCHAR on the left with spaces or zeroes (your preference) and move it to the CHAR field. Best practice says that unless the lengths are guaranteed to match exactly, you must ALWAYS pad anything being moved to a CHAR field.

For example:

inputlink.Integer = svVarChar
Right(("00000000":svVarChar),8) = svChar

This would have your numbers come out like this:
00123456
00654321
00000001

Instead of calculating the exact length of the field and add the exact number of zeroes, it is faster to prepend enough to cover the worst case (empty field) and then substring the eight you need to fill the CHAR field.

[chulett]

It's a CHAR, they pad on the right as any other string would. Nothing "bad" about that. Now, if you need them left-padded / zero filled, that's a whole different requirement as you've addressed.

[Edit] So noted, modified to say "bad for a number", which it usually is - Andy

[srini.dw]

Thanks for the time and replies.

@srinivas.g set is AL32UTF8.

@Andy,

Hex value for ? is 3F

inputlink.Integer = svVarChar ->Varchar(8)
Right(("00000000":svVarChar),8) = svChar -> Char(8)

Iam getting the below values.
00123456
00654321
00000001

But again when I want to remove the leading "0" it with function Trim(svChar,"0","L") getting the below values as output.

123456??
654321??
000001??

I have tried the logic If svChar[1,2] ="00" Then svChar[3,8] Else svChar
getting the below values.
123456??
654321??
000001??

But when I checked the value of svChar[3,8] in peak stage, its coming as 123456

But svChar[3,8] when mapped directly to target table value is coming as 123456??

Any thoughts.

Thank You,

[ArndW]

You problem is that your target field is 8 characters long, no more and no less; each position needs to be given a value. So if your value is 6 characters long, what do you expect the remaining 2 to be?

The solution with using a VarChar() and leading zeroes is a viable one. Normally when dealing with numbers your only choice is to use leading "0" or " " (zeroes or spaces).

The TRIM() Function will remove those characters, but since the result needs to be 8 characters long, it will fill these two position with what you see as "?". Basically, you cannot TRIM() a CHAR(n) column, so DataStage will implicitly conver your CHAR() to a VarChar(), perform the trim and then convert the VarChar() back to a CHAR().

[srini.dw]

Thanks for the reply.

We are changing the target datatype to Varchar(8)

Thanks,

[ArndW]

That is by far the best solution.