left join restrictions that openSQL poses (Discussion)

[Former Member]

1)

I wonder why opensql throw up this limitation that I can only use only ONE table left

join table in a sql statement. No sql flavor that I know, restrict this.

<i>This is not a urgent question and I can easily live with it, the sql statements here are mere

examples produced by me to sound more understandable, so don’t want to get them fixed.</i>

SELECT vbpa~vbeln
INTO TABLE itab
FROM vbap LEFT JOIN vbpa
ON vbap~vbeln = vbpa~vbeln AND
   vbap~posnr = vbpa~posnr
left join vbkd on vbpa~vbeln = vbkd~vbeln    
.

The error compiler put forward is:

Unable to compare with "VBPA~VBELN". A table can be joined with a 
maximum of one other table using LEFT OUTER JOIN.

If I change it to :

SELECT vbpa~vbeln
INTO TABLE itab
FROM vbap LEFT JOIN vbpa
ON vbap~vbeln = vbpa~vbeln AND
   vbap~posnr = vbpa~posnr
left join vbkd on vbap~vbeln = vbkd~vbeln    
.

Then it becomes a valid openSQL statement

Any comment.

2)

Why can not I use where condition from a column of a table participating as a left join

table.

DATA: itab TYPE STANDARD TABLE OF vbeln.

SELECT vbpa~vbeln
INTO TABLE itab
FROM vbap LEFT JOIN vbpa
ON vbap~vbeln = vbpa~vbeln AND
   vbap~posnr = vbpa~posnr 
Where vbpa~parvw = 'WE'.

The error compiler put forward is:

No fields from the right-hand table of a LEFT outer Join 
may appear in the WHERE condition:"vbpa~parvw".

<i>I understand that the same can be translated into a INNER join sql, but in some cases

what I need is a left join. In course of discussion we can think of cases where I cant live

without a left join</i>

[Former Member]

Flora,

When it says 'LEFT' join, it means that table in the <b>Right</b> may or may not corresponding data for row from table in the left but all the rows satisfying criteria will be included. Here is simple example

Table L Table R

Key field next field key next field

10 A 10 B

20 C 20

The left join will give the following result

10 A B

20 C

Since the data from the right table may be null, it does not make any sense to apply any condition or make any further left join on that. If open SQL had allowed that, the result would have been same as inner join. This syntax is actually <b>NOT</b> restricting any functionality.

I may see problem in one scenario when you want to have anotert left join from non-key field of table on right with third table. May be SAP restricted this so that open sql can run on multiple DBMS.