Skip to Content

Conversion in Join statement

Jan 24, 2017 at 10:32 AM


avatar image

Hi SAP experts,

I have a join statement with 2 different data types. The first one is char 10 with alpha conversion and the second one char 4 with alpha conversion as well. The join statement is like this:

inner join on tab1-char10 = tab2-char4

The data types are like this:

char 10: 0000000001

char 4: 0001

Do you have any advice how to convert these data in the join statement? The expected result is that join condition is met in the example above.

Thanks in advance!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Horst Keller
Jan 25, 2017 at 07:00 AM

I guess you are talking about Open SQL.

The comparison between the columns of an ON condition is carried out at the database and the comparison rules are database specific. In that special case either there is a truncation on the right of the longer field or the shorter field is filled up with blanks (that should be the canonic behavior). Both would give non equal values for your above example.

If your release already allows it, a solution would be to use LPAD on the LHS to fill up the shorter field with 000 on the left, (up to now, no expressions are allowed on the RHS). Otherwise you might use Native SQL.

10 |10000 characters needed characters left characters exceeded
Jeremy Good
Jan 24, 2017 at 07:51 PM

It the field is always zero padded like shown in 'char 10', then simply concatenate '00000000' to your field, and right trim in your SQL language of choice to the appropriate # of characters. Alias the field and use it in your join expression.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Goes into the right direction, but in Open SQL it would not work to alter the fields in the SELECT list because you can use the alias only behind ORDER BY and nowhere else. Maybe it works in Native SQL.


Hi Horst - thanks for setting me straight. After I posted my original answer I thought about the primary tag of ABAP Development, and realized that my answer was more along the lines of SQL or Oracle syntax, where I have some past experience with data items like this where not all fields are the same fixed length and the padding is inconsistent.

umayaraj B Jan 25, 2017 at 04:06 AM
shift data1 left deleting leading '0'.  " char 10
shift data2 left deleting leading '0'.  "char 4
...where data1 = data2.
Show 1 Share
10 |10000 characters needed characters left characters exceeded

This is not an answer to the question ...