Search code examples
abapopensql

Ignore leading zeros in JOIN condition?


I want to compare the field bseg~zuonr with aufk~aufnr in an inner join SQL select.

SELECT bseg~hkont, bseg~zuonr, bseg~belnr, bseg~gjahr, aufk~prctr FROM bseg INNER JOIN aufk
  ON bseg~zuonr = aufk~aufnr "<--
  WHERE bseg~hkont IN @s_hkont
  INTO TABLE @DATA(output).

This select is not working right for me and not giving back any data.

I think my problem is, that aufk~aufnr has leading zeros (for example: 000072667023) and bseg~zuonr contains only the number without leading zeros (for example: 72667023).

I have tried to use the TRIM-function inside the SQL select but that did not worked for me (Code snipped: ON bseg~zuonr = TRIM( LEADING '0' FROM aufk~aufnr )"(" is not allowed here. "." is expected.)

Did I do something wrong? Do you know any solution for this issue?


Solution

  • You could use the concat function. For example

    SELECT bseg~hkont, bseg~zuonr, bseg~belnr, bseg~gjahr, aufk~prctr 
      FROM bseg 
      JOIN aufk ON concat( '0000', bseg~zuonr ) = aufk~aufnr
     WHERE bseg~hkont IN @s_hkont
      INTO TABLE @DATA(output).
    

    Also you can combine this with substring to set the aufnr exact to 12 chars