04-15-2016 10:48 AM
Hey,
I'm trying to join three database tables in my ABAP report.
I have an input local table with ID's in it. So I use "FOR ALL ENTRIES IN" on this table.
The data for the result table comes from three database tables.
The first one (names) contains ID and NAME,
the second one (buildings) contains ID, BUILDING and PRIORITY
and the third one (details) contains BUILDING, DESCRIPTION, LOCATION, COUNTRY, INDEX and TYPE.
(The red ones are the fields which I want to have in the result table.)
The result table should look like:
My SQL Code is:
SELECT
a~id
a~name
b~building
c~description
c~location
c~country
FROM names AS a
LEFT JOIN buildings AS b
ON b~id = a~id
AND b~priority = '3'
LEFT JOIN details AS c
ON c~building = b~building
AND c~index = '01'
AND c~type = 'B'
INTO CORRESPONDING FIELDS OF TABLE et_result
FOR ALL ENTRIES IN lt_input
WHERE a~id = lt_input-id.
This code looks right for me, but ABAP's syntax check says:
Line 01: "The elements in the "SELECT LIST" list must be separated using commas.
This error only occurs when using LEFT JOIN two times. When I make first a LEFT JOIN and second a JOIN the syntax check says "okay".
But then I will get the false results, because all lines of result table with NULL values will be deleted. And that's not what I want.
I hope you can help me!
Best regards,
Mark
04-15-2016 11:25 AM
It's strange the syntax check alerts here.
But corresponding to this message, I interpret, that you are on NW 7.4 >= SP08.
In general it is recommanded to use the new Open-SQL syntax. This means, you have to separate your fields with comma and use @ for variables. In your case:
- SELECT
- a~id,
- a~name,
- b~building,
- c~description,
- c~location,
- c~country
- FROM names AS a
- LEFT JOIN buildings AS b
- ON b~id = a~id
- AND b~priority = '3'
- LEFT JOIN details AS c
- ON c~building = b~building
- AND c~index = '01'
- AND c~type = 'B'
- INTO CORRESPONDING FIELDS OF TABLE @et_result
- FOR ALL ENTRIES IN @lt_input
- WHERE a~id = @lt_input-id.
04-15-2016 11:20 AM
04-15-2016 11:33 AM
I have seen this post before. But in my case all conditions which are related to the join tables are already in the join conditions...
But thanks anyway
04-15-2016 11:25 AM
It's strange the syntax check alerts here.
But corresponding to this message, I interpret, that you are on NW 7.4 >= SP08.
In general it is recommanded to use the new Open-SQL syntax. This means, you have to separate your fields with comma and use @ for variables. In your case:
- SELECT
- a~id,
- a~name,
- b~building,
- c~description,
- c~location,
- c~country
- FROM names AS a
- LEFT JOIN buildings AS b
- ON b~id = a~id
- AND b~priority = '3'
- LEFT JOIN details AS c
- ON c~building = b~building
- AND c~index = '01'
- AND c~type = 'B'
- INTO CORRESPONDING FIELDS OF TABLE @et_result
- FOR ALL ENTRIES IN @lt_input
- WHERE a~id = @lt_input-id.
04-15-2016 11:35 AM