Conversion in Join statement

[gerrit_meyer4]

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!

[jcgood25]

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.

[horst_keller]

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.

[jcgood25]

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]

shift data1 left deleting leading '0'.  " char 10
*
shift data2 left deleting leading '0'.  "char 4
*
...where data1 = data2.
  

[horst_keller]

This is not an answer to the question ...

[horst_keller]

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.