Join number field stored as string

building reports crystal reports
1

Is there a workaround to join number fields stored as string with different lengths? For ex. Table A has field of length, so the value 10 comes over as ‘00010’ in CR. This field is to be joined to table B field of length 8; here the value 10 comes over as ‘00000010’. Direct join is not working. In case of relational database I could manipulate the SQL. But I am using SAP BW DSO tables as data data source and the option of freehand SQL is not present

Abhi719 :us: (BOB member since 2010-12-10)

2

Additional Information please : what do you expect the end result to be ?

phwrd :us: (BOB member since 2010-10-22)

3

I want to able to join the fields in the database expert. I am unable to do that now as the fields are of different lengths

Abhi719 :us: (BOB member since 2010-12-10)

4

Are you trying to add them together and keep the leading zeros ?

phwrd :us: (BOB member since 2010-10-22)

5

Hi,

The only 2 things that come to my mind are

  1. Leave the fields unjoined in the report and have every field in the report be a formula that converts the fields and checks that to they are a match before showing a value. This is an ugly way to go and it wont work well if there is alot of data in the tables since the formaulas are doing what should be the joins job.

  2. If you can do freehand sql against any other database or even just load an excel sheet that has all the possible numbers for both tables in 2 columns, you could create a pass thru table. Basically join the 2 tables on each column in this one. This will perform pretty well but it might be a maintenence nightmare if the numbers change alot and you cant find a way to auto update the passthru table.

Hope this helps

James

jte13 :us: (BOB member since 2008-07-09)