Outer Join (select) statement

[Former Member]

hI

Im BW-Consultant. I have two tables with some fileds.

ZTABLE1 ---> MATNR, PRCTR, BUKRS, LIGNG, MATGR.And this table has 5-materials

ZTABLE2 --> MATNR, PRCTR, BUKRS, FKMNG, VERID. And this table has 10-materials.

These two tables(ZTABLE1, ZTABLE2) has 3-materials are common.

But i want all 15-materials in an another table ZTABLE3. So how can i write an <b>outer join statement.</b> .

Please give the OuterJoinCode(select statement) to get all 15-materials

regards

kumar

[Former Member]

Example

SELECT KNA1~KUNNR KNA1~ADRNR ADR6~SMTP_ADDR 
INTO (A, B, C) 
FROM KNA1 LEFT OUTER JOIN ADR6 
ON KNA1~ADRNR = ADR6~ADDRNUMBER 
WHERE KUNNR BETWEEN '0000000000' AND '0000500000'.

*-----------------------------------

DATA: BEGIN OF wa,
        carrid   TYPE scarr-carrid,
        carrname TYPE scarr-carrname,
        connid   TYPE spfli-connid,
      END OF wa,
      itab LIKE SORTED TABLE OF wa
                WITH NON-UNIQUE KEY carrid.

SELECT s~carrid s~carrname p~connid
  INTO CORRESPONDING FIELDS OF TABLE itab
  FROM scarr AS s
       LEFT OUTER JOIN spfli AS p ON s~carrid   =  p~carrid AND
                                     p~cityfrom = 'FRANKFURT'.

LOOP AT itab INTO wa.
  WRITE: / wa-carrid, wa-carrname, wa-connid.
ENDLOOP.

A

[Former Member]

Hi Ravi,

You can not have the total materials from two tables using JOIN statements. Join statement will consider only common materials ( or ) materials from one of tables using OUTER JOINS.

If you want only the common fields MATNR, PRCTR and BUKRS into ZTABLE3 then use APPEND statement.

<b>APPEND LINES OF ZTABLE1 TO ZTABLE3.

APPEND LINES OF ZTABLE2 TO ZTABLE3.</b>

Now, ZTABLE3 will have total 15 materials including common materials in ZTABLE1 and ZTABLE2.

<b>Note: </b> You have many open threads. Please close the threads if they are solved/answered.

Thanks,

Vinay

[Former Member]

hI

can i do the left outer join on Structure . Please let me know

kumar

[Former Member]

Hi Ravi,

You can not do JOIN operation on structures but can perform only on tables.

Thanks,

Vinay