Data Services 3.0 adds a "rtrim" function to one field in the where clause

[Former Member]

I had a fairly simple self join in DS 3.0 but found out that when generating optimized SQL, DI puts a rtrim() function on one column in the where clause. The two columns joined together have identical data type, varchar(9). There is a trailing space in both columns so the extra rtrim caused the join returning an empty set (there are matching rows if rtrim() is removed). I have no idea why rtrim() is generated and how to get rid of it. Please help!

Thanks,

Larry

[werner_daehn · Accepted solution]

According to ANSI SQL, the condition 'A ' = 'A' should evaluate to true. Trailing spaces should not matter. Oracle behaves differently and then join might give you different results depending on if it is done inside the engine with ANSI rules or inside the Oracle database which does a binary comparison. There is no clear rule when you want what so we try to dampen that a bit.

varchar = varchar........here we guess there are no trailing blanks and hence both will return the same result. Obviously, if one of the varchars does have a trailing blank you still would get different results but hey, you have to draw the line somewhere.

char = char.....with same length is simple both have trailing blanks per definition.

The big problem is the case below and similar:

varchar = char....because here in Oracle you would never get a result unless the varchar has as many blanks to fill it up to the char datatype length.

In such a scenario we assume a rtrim() to be a save solution even if it costs performance. The fun thing is, DI does not know a char datatype, only varchar. So as seen from Designer both columns look identical. However, during an import, the varchar gets a flag that this is actually a char datatype.

If you want to get rid of the rtrim you have to clear the flag. The easiest way is to open the table from the object library. Rightclick that varchar column and change the datatype to date. Click okay. Then rightclick the column again and change it back to varchar.