Dynamic join condition in abap

[thfwjkr]

Hi guys!

i want to join two tables lfa1 and pa0000.

lfa1-lifnr data is like A12345678 and pa0000-pernr is like 12345678.

my select query is this.

SELECT A~LIFNR FROM LFA1 AS A
INNER JOIN PA0000 AS B ON A~LIFNR+1(8) = D~PERNR <- this part

INTO CORRESPONDING FIELDS OF TABLE IT_LFA1
WHERE B~ENDDA = '99991231'
AND B~MASSN = 'TT'.

this part has a problem.

i solved this selecting two tables seperately and using read table in loop but i think there is another better way.

Thanks in advance 🙂

[thfwjkr]

Hi guys!

i want to join two tables lfa1 and pa0000.

lfa1-lifnr data is like A12345678 and pa0000-pernr is like 12345678.

my select query is this.

SELECT A~LIFNR FROM LFA1 AS A
INNER JOIN PA0000 AS B ON A~LIFNR+1(8) = D~PERNR <- this part

INTO CORRESPONDING FIELDS OF TABLE IT_LFA1
WHERE B~ENDDA = '99991231'
AND B~MASSN = 'TT'.

this part has a problem.

i solved this selecting two tables seperately and using read table in loop but i think there is another better way.

Thanks in advance 🙂

[FredericGirod]

Did you try to add LFB1 in your join and use LFB1~PERNR ?

[thfwjkr]

Unfortunately, pernr field is empty in lfb1

[FredericGirod]

Is it a real link made with customizing or just a "short cut" used by functional team ?

if it is a real link, you should find the field PERNR populated in a LF.. table

[thfwjkr]

it's just a short cut and i couldn't find any pernr data in a LF.. table.

[Sandra_Rossi]

You say "this part has a problem", what problem? Syntax error? Any message?

[thfwjkr]

it has a syntax error.

Field "A~LIFNR" is unknown. It is neither in one of the specified

tables nor defined by a "DATA" statement . . . . . . . . . .

[thfwjkr]

on the query i wrote d~pernr but its a typo. original query is b~pernr

[Sandra_Rossi]

+1(8) is not valid, it cannot be used on table columns, only on ABAP variables:

A~LIFNR+1(8)

From ABAP 7.50, you may use the following SQL function:

SUBSTRING( A~LIFNR, 2, 8 )

[thfwjkr]

Thank you for your kind reply.

[keremkoseoglu]

If you split your query into multiple CDS views, you can achieve your goal easily.

First CDS view (called ZZ1) should return a list of LIFNR+1(8) values.

You can now join ZZ1 with any other table / view you like.