topic Re: Outer Join (select) statement in Application Development and Automation Discussions

Outer Join (select) statement

Former Member — Fri, 23 Nov 2007 16:50:27 GMT

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 outer join statement. .

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

regards

kumar

Re: Outer Join (select) statement

Former Member — Fri, 23 Nov 2007 16:55:01 GMT

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.

Re: Outer Join (select) statement

Former Member — Fri, 23 Nov 2007 17:20:32 GMT

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.

APPEND LINES OF ZTABLE1 TO ZTABLE3.

APPEND LINES OF ZTABLE2 TO ZTABLE3.

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

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

Thanks,

Vinay

Re: Outer Join (select) statement

Former Member — Fri, 23 Nov 2007 17:56:51 GMT

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

kumar

Re: Outer Join (select) statement

Former Member — Fri, 23 Nov 2007 18:06:55 GMT

Hi Ravi,

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

Thanks,

Vinay