Ignore leading zeros in JOIN condition?

Question

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?

bseg~zuonr is of type char(18) and aufk~aufnr is of type char(12) — schmelto, Oct 17 '22 at 08:32

score 3 · Accepted Answer · edited Nov 19 '22 at 05:32

3

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

edited Nov 19 '22 at 05:32

Suncatcher

10,355
10
52
90

answered Oct 17 '22 at 20:26

Jünge alles

491
1
5
19

1

it worth mentioning that this syntax is available only since 750 – Suncatcher Nov 19 '22 at 05:31

schmelto · Answer 2 · 2022-11-21T09:10:41.303

0

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

is also working

edited Nov 21 '22 at 09:10

answered Nov 18 '22 at 09:55

schmelto

427
5
18

Ignore leading zeros in JOIN condition?

2 Answers2