Online Tutorials & Training Materials |
Register Login

JOIN in open SQL

27 Apr 2007 9:36 am || 0

The condition in the ON clause has some limittations. See below.


One or more lines on the right-hand table is linked to a line in the
left-hand table. Lines from the left-handed table
is only selected if they meet the ON criteria.

FROM from vbak as a inner join vbap as b
ON b~vbeln = a~vbeln.

One or more lines from vbap is selected for each line in vbak.

Limits on the ON clause:


The left outer join read lines from the left-handed table EVEN if there is

no corresponding line in the right hand table.

FROM vbak as a left outer join vbap as b
ON b~vbeln = a~vbeln.

If vbap does not contain any lines that meets the condition, a single line
where the values from vbap is filled with null values.

Example 1

SELECT a~zafstemnr b~zafstemnr b~zsaknr
FROM zcostafstm as a INNER JOIN zcostplan as b
ON b~zafstemnr = a~zafstemnr.

Example 2

SELECT lips~vbeln lips~lfimg likp~wadat

INTO corresponding fields of table ltab_orders
FROM lips JOIN likp on ( lips~vbeln = likp~vbeln )
JOIN vbfa on ( lips~vbeln = vbfa~vbelv )
WHERE lips~matnr = matnr
and lips~vbeln in vbeln
and likp~vbeln in vbeln
and vbfa~vbelv in vbeln
and lips~werks in werks
and likp~wadat in datum
and vbfa~vbtyp_n = 'Q' "WMS transfer order
and vbfa~vbtyp_v = 'J'. "Delivery