[SOLVED] Ignore leading zeros in JOIN condition?

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