Establishing a Relationship Where The Data Fields Are Padded With Blanks

[pdaprem]

I need t establish a relationship between two tables where the data fields I need to join on are padded with blanks in both tables. The data characteristics of the fields in both tables is identical, both are Character Lenght 16, Precision 20, Data Size 42.  I'm using a RTRIM in the Relatinship Expression to removed the blanks. The Relationship creates a Left Outer Join.  Use of the RTRIM is causing serious performance issues.  Is there a good alternative to using RTRIM.

[blom0344]

Why remove padding in the first place if the fields to be joined contain identical strings?

[wyconian]

I'd pass that back into the etl and get your etl designer to tidy up the data before it gets to your model

[pdaprem]

Removing the padding becuase if I don't no records are returned.

[pdaprem]

Wuld love to be able to get a redesign but that ins't likely yo happen.

[blom0344]

What happens if you define an SQL query subject that explicitly combines both table data like:

Code Select Expand


select x.*,y.* from table1 as x inner join
table2 as y
on rtrim(x.field1) = rtrim(y.field1)


Does that return the proper data ?

[cognostechie]

I need t establish a relationship between two tables where the data fields I need to join on are padded with blanks in both tables. The data characteristics of the fields in both tables is identical, both are Character Lenght 16, Precision 20, Data Size 42.  I'm using a RTRIM in the Relatinship Expression to removed the blanks. The Relationship creates a Left Outer Join.  Use of the RTRIM is causing serious performance issues.  Is there a good alternative to using RTRIM.

I don't think it is using the Left Outer Join because of RTRIM. It's because the cardinality defined in the join is 0..1  or 0..N on any one side of the relationship. If you use RTRIM or LTRIM(RTRIM(  ) which is better, it will still use an inner join if the relationship is 1:1 on both the sides or even 1:1 and 1:N between both the query subjects.

Have you checked the cardinality ?

[pdaprem]

cignosetechie, Correct, the Left Outer Join is not caused by the RTRIM. It is caused by the cardinality which is setup porperly for my data. The issue is the RTRIM which seems to have performance implications.

[pdaprem]

We did some performance tuning on the database and the Relationship is working fine without the RTRIM. Hopefully the performance will remain good.  Thanks to all who responded!!