Right Function

[kittu.raja]

Hi,

I have a column by name Policy Number of data type char and length 15.

The input data for that column varies.

I want my ouput to be length 9.
In the transformer
I am using Right("000000000000000":Trim(Policy Number),9)
But i am getting only last 2 digits instead of 9 digits.

I tried with one record 31256145

My output should be 031256145

But I am getting 45.

Can anybody help me out.

Thanks,

[gssr]

Use StringToDecimal Function with taregt column length of 9.
If you need the Target datatype to be Char, you can change it back

[ArndW]

Assuming your output is a Char() or VarChar() and the input is a Char(9), then "Right('000000000':Trim(In.PolicyNumber),9)" will product what you require. I see no explanation for your given output of "45", unless you have made an error somewhere with the column lengths.

[ray.wurlod]

With what character is the Char(15) field padded? Is it possible that Trim() is having no effect?

[kittu.raja]

With what character is the Char(15) field padded? Is it possible that Trim() is having no effect?

Hi Ray,

The field is not padded with anything.

Probablt you are right. Trim has no effect

[ArndW]

I assumed that PX would implicitly convert the CHAR to a VARCHAR; since one cannot trim a CHAR field; but perhaps this isn't happening in your case. Try creating a VarChar stage variable, assign it the value Trim(In.PolicyNumber) then use that stage variable for your right() function.

[priyadarshikunal]

I tested the same logic(trim) and it works just fine. Hence I think the field is right padded with other character than space.

Can you check whats the APT_STRING_PADCHAR defined in environment variable, also please add a derivied column in transformer and do seq(In.PolicyNumber) and let us know the result in that column.

Also, I have seen some problem with char data type read with ODBC connector stage, hence let us know the version of datastage, source (database/flat file) and stage used to read data.

[kittu.raja]

I tested the same logic(trim) and it works just fine. Hence I think the field is right padded with other character than space.

Can you check whats the APT_STRING_PADCHAR defined in environment variable, also please add a derivied column in transformer and do seq(In.PolicyNumber) and let us know the result in that column.

Also, I have seen some problem with char data type read with ODBC connector stage, hence let us know the version of datastage, source (database/flat file) and stage used to read data.

Hi,

Its not right padded with any space. I also thought the same thing but did not find any spaces after the record.

I am reading from dataset and loading into another dataset.

I am just reading one column, doing this transformation and loading that column into another dataset

I will try to use this environmental variable and i will let you know.

Thanks,

[ray.wurlod]

It's padded with something - probably 0x00 characters. What is the value of APT_STRING_PADCHAR environment variable?

[kittu.raja]

It's padded with something - probably 0x00 characters. What is the value of APT_STRING_PADCHAR environment variable?

The value is 0x0

Thanks,

[shivajid2k]

How frequently the length of column is changing? I mean check distinct column length.

[shivajid2k]

Right function usually trims the fields. Try this out.
Create a vaiable with logic something like this.

If the lenght(Column)=7 then '00':Column else....

use same logic for other lengths as well