PadString is constrained to the input column length

[PhilHibbs]

Input:
HOUSE_NUM VarChar(10)
FLAT VarChar(20)
HOUSE_NAME VarChar(30)
DISTRICT VarChar(30)
STREET VarChar(30)
POST_TOWN VarChar(30)
COUNTY VarChar(30)
POSTCODE Char(8)

Output: ADDR_TX VarChar(440)

Derivation: PadString( in.FLAT, ' ', 40 )[1,40] : PadString( in.HOUSE_NAME, ' ', 40 )[1,40] : PadString( in.HOUSE_NUM, ' ', 40 )[1,40] : PadString( in.STREET, ' ', 40 )[1,40] : PadString( in.DISTRICT, ' ', 40 )[1,40] : PadString( in.POST_TOWN, ' ', 40 )[1,40] : PadString( in.COUNTY, ' ', 40 )[1,40] : in.POSTCODE

The result is that the FLAT is padded to 20 characters, then the HOUSE_NAME is padded to 30, then the HOUSE_NUM padded to 10, etc.

I've searched and found people having problems with PadString before, but can't see a satisfactory explanation of this behaviour. It appears that PadString will not return a result that is longer than the maximum length of each input column. Is this normal and correct? It is not clear from the documentation that this should happen.

[ArndW]

I thought that rather strange, but wrote a test program on my machine and it does seem as if they've limited the maximum output of PadString to the maximum length of the input string.

In your case "In.FLAT:Spaces(40-Len(In.FLAT))" will work instead.

[PhilHibbs]

What I've done is made a CHAR(40) stage variable for each address element and just mapped the inputs into those and then concatenated the stage variables into the output. No function calls necessary!

[PhilHibbs]

What I've done is made a CHAR(40) stage variable for each address element and just mapped the inputs into those and then concatenated the stage variables into the output. No function calls necessary!

[ArndW]

This should be reported to IBM as a bug, the fix should be quite easy as it just seems to be a bad datatype definition.