Skip to Content

Conversion in Join statement

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    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.

    Add comment
    10|10000 characters needed characters exceeded

  • 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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • 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.
    Add comment
    10|10000 characters needed characters exceeded