>>>Hi,
>>>
>>>If you run this code below you will see that the column fld1_fld2 of the resulting query does not have the entry in FLD2 for the first 3 records. The difference is that the first 3 records have 6 charecter entry and they are padded with more of & nbsp. Anybody see why?
>>>
>>>
>>>CREATE CURSOR test (fld1 c(15), fld2 c(35))
>>>INSERT INTO test (fld1, fld2) VALUES ("123456","Descripton of 123456")
>>>INSERT INTO test (fld1, fld2) VALUES ("334343","Descripton of 334343")
>>>INSERT INTO test (fld1, fld2) VALUES ("337743","Descripton of 337743")
>>>INSERT INTO test (fld1, fld2) VALUES ("3343430000000","Descripton of 3343430000000")
>>>
>>>select *, ALLTRIM(FLD1) + REPLICATE( ' ', 15-LEN(ALLTRIM(FLD1)) ) + ' ' + ALLTRIM(FLD2) AS FLD1_FLD2 from test
>>>
>>
>>Because the field initial length is too short:
>>
>>select *, ;
>> padr(ALLTRIM(FLD1) + REPLICATE( ' ', 15-LEN(ALLTRIM(FLD1)) ) + ' ' + ALLTRIM(FLD2),200) ;
>> AS FLD1_FLD2 ;
>>from test
>>
>
>Interesting. I don't understand why PADR() makes all the difference. But it works. Thank you.
BTW, instead of PADR you can also use CAST (my expression as varchar(200))
That's the trick I recently used when I made a similar mistake (forgot to define a length for the final column in the select statement).
If it's not broken, fix it until it is.
My Blog