topic Re: multiple left outer join in single select query in Application Development and Automation Discussions

multiple left outer join in single select query

Former Member — Mon, 07 Jul 2008 11:34:08 GMT

My client requires to apply multiple left outer join in single select query.

Tell me how it is possible . tell me if we can use native sql query.

Re: multiple left outer join in single select query

Former Member — Mon, 07 Jul 2008 12:03:33 GMT

Hi Vineet Kumar,

My suggestion would be better go for a VIEW. If u can't go for View's Use LDB this might solve ur issue.

Regards,

Dheepak

Re: multiple left outer join in single select query

Former Member — Mon, 07 Jul 2008 12:13:24 GMT

good

check this code and try accordingly.

SELECT *

FROM table1 AS t1

LEFT OUTER JOIN table2 AS t2

ON t2_{col2 = t1}col2

INTO CORRESPONDING FIELDS OF TABLE result

WHERE t1~col1 IN so_col1.

hope this will help you to solve your problem.

Thanks

mrutyun^

Re: multiple left outer join in single select query

Former Member — Mon, 07 Jul 2008 12:32:25 GMT

HI vineet,

Using multile left join is possible but it reduce performance as it takes a long time to retrieve data.

The following code would help you in knowing how to use multiple left join

REPORT  test.

PARAMETERS:
  p_auart LIKE vbak-auart.

DATA:
  BEGIN OF fs_sales,
    auart LIKE vbak-auart,
    vbeln LIKE vbak-vbeln,
    posnr LIKE vbap-posnr,
    matnr LIKE vbap-matnr,
    netwr LIKE vbap-netwr,
  END OF fs_sales,
  fs_temp LIKE fs_sales.

DATA:
  t_sales LIKE STANDARD TABLE OF fs_sales.

SELECT k~auart
       k~vbeln
       p~posnr
       p~matnr
       p~netwr
  INTO TABLE t_sales
  FROM vbak AS k
  OUTER JOIN vbap AS p
  ON k~vbeln = p~vbeln
 WHERE auart = p_auart
   AND p~netwr LT 1000.


IF sy-subrc EQ 0.
  WRITE:/5 'Sales Document'(001),/,
        15 'Sales Document Item'(002),
        40 'Material Number'(003).

  sort t_sales by netwr ascending.
  LOOP AT t_sales INTO fs_sales.
    WRITE:/ fs_sales-vbeln under text-001.
    LOOP AT t_sales INTO fs_temp WHERE vbeln = fs_sales-vbeln.
      WRITE:/ fs_temp-posnr under text-002,
              fs_temp-matnr under text-003.
    ENDLOOP.
  ENDLOOP.
ELSE.
  MESSAGE 'No Record Found' TYPE 'I'.

ENDIF.

But i would suggest you to go with views instead of using multiple joins..

Best of luck,

Bhumika