topic Re: Conditioins in outer join in Application Development and Automation Discussions

Conditioins in outer join

marcin_cholewczuk — Wed, 30 Jun 2010 14:52:38 GMT

Hi Gurus,

Is it possible to put codition on outer joined table? In where it's not possible, cause I'm getting syntax error. With puting it under HAVING I'm able to go pass syntax check, but I'm getting short dump during execution. It looks like

SELECT *

FROM tab1 LEFT JOIN tab2 ON ....

WHERE...

GROUP BY...

HAVING tab2~field1 IS NULL

Any ideas how it can be done?

Marcin Cholewczuk

Re: Conditioins in outer join

ThomasZloch — Wed, 30 Jun 2010 15:30:27 GMT

When you encounter syntax errors or short dumps, please decribe these in all detail to put people in the position to help you. Also provide actual code and not place holders, which might not contain the actual cause of the problem.

Regarding WHERE-conditions for outer joined tables, only those fields are not allowed in the WHERE-condition that you already use in the ON-condition to link the two tables. For background, see

http://help.sap.com/abapdocu_70/en/ABAPSELECT_JOIN.htm

Thomas

Re: Conditioins in outer join

marcin_cholewczuk — Thu, 01 Jul 2010 07:32:12 GMT

Hello Thomas,

It's not possible to use any field from right table when I use LEFT JOIN in WHERE condition. I'm always getting syntax error "No fields from the right-hand table of a LEFT OUTER JOIN may appear in the WHERE condition: <field name>.". However it looks like I can move this condition to ON condition in JOIN. Not sure if it will work how I want, but at least I'm not getting syntax errors or short dumps (DBIF_RSQL_SQL_ERROR - SQL error in the database when accessing a table).

Marcin Cholewczuk