SAP BODS join (issue with joining 2 varchar fields)

[Former Member]

Hi,

  I am trying to join output of two queries and joining on a varchar(255) field. Field Eg: 20150706C-4555510240-LA300007201500

For some reason the fields are not joining though I know there are records with matching fields. I tried ltrim(rtrim()) for both the inputs, did not work.
I tried a cast( varchar255) just to make sure dint work either.

Any suggestion is appreciated, having a hard time to do the join

Am using version 14.2.2.446

Cheers!

[former_member208363]

Hi Josh J

Did you resolve your issue?

[former_member198401]

Hi Josh,

Can you execute the SQL on database to check if it is returning rows or not.  Do you have any other integer column or key column in both the tables which can be joined.

Regards

Arun Sasi

[former_member187605]

Copy and paste the generated SQL (from the DS Designer menu: Validation > Display Optimized SQL) and run it directly against your database. Does it work there as expected?

[Former Member]

You can troubleshoot this issue in multiple ways,

1. Write the output of both the queries to two database tables and compare them.
2. Try to get just first X (20) characters from each string and join based on the substring
3. Compare the length of each value
4. Apply checksum (MD5 Hash) on the columns and compare the values

Cheers

Mohan

[Former Member]

Hi Josh,

Try this join in the database end if it works or not first, seems Varchar with 255 size is giving the problem.

Regards

[former_member106536]

Run it in Debug mode and see if it works.