topic Re: Inner join 3 tables in Application Development and Automation Discussions

Inner join 3 tables

Former Member — Wed, 04 Mar 2009 17:22:27 GMT

Hi guys,

I would like to join 3 tables so my code is:

SELECT IFLS_ADDR~TPLNR  IFLS_ADDR~PLTXT
       ZDRMIFLOT001~QMNUM QMEL~QMTXT QMEL~ZZPOTSOL QMEL~OBJNR
INTO CORRESPONDING FIELDS OF TABLE IT_ALV  FROM
ZDRMIFLOT001 INNER JOIN IFLS_ADDR
          ON ZDRMIFLOT001~TPLNR = IFLS_ADDR~TPLNR
          INNER JOIN QMEL
          *ON QMEL~QMNUM = ZDRMIFLOT001~QMNUM*
WHERE IFLS_ADDR~TPLNR IN S_TPLNR
     AND   IFLS_ADDR~FLTYP IN TIPO
     AND   IFLS_ADDR~POST_CODE1 IN P_CODE1
     AND   IFLS_ADDR~CITY1 IN P_CITY1
     AND   IFLS_ADDR~STREET IN P_STREET.

The problem is:

if field QMNUM is empty the inner join doesn't work well I loose information because even though

QMNUM is empty I would like to inform the other fields of it_alv.

Thanks a lot.

Re: Inner join 3 tables

Former Member — Wed, 04 Mar 2009 17:28:47 GMT

I think you need to use a left join on QMEL

Re: Inner join 3 tables

Former Member — Wed, 04 Mar 2009 17:29:11 GMT

Hi,

you can try this way..

SELECT IFLS_ADDR~TPLNR  IFLS_ADDR~PLTXT
       ZDRMIFLOT001~QMNUM 
INTO CORRESPONDING FIELDS OF TABLE IT_ALV  FROM
ZDRMIFLOT001 INNER JOIN IFLS_ADDR
          ON ZDRMIFLOT001~TPLNR = IFLS_ADDR~TPLNR
WHERE  IFLS_ADDR~TPLNR IN S_TPLNR
     AND   IFLS_ADDR~FLTYP IN TIPO
     AND   IFLS_ADDR~POST_CODE1 IN P_CODE1
     AND   IFLS_ADDR~CITY1 IN P_CITY1
     AND   IFLS_ADDR~STREET IN P_STREET.
IF SY-SUBRC EQ 0.
SELECT QMTXT ZZPOTSOL OBJNR
  FROM QMEL
  INTO TABLE ITAB
   FOR ALL ETRIES IN  IT_ALV
   WHERE QMNUM = IT_ALV-QMNUM.
ENDIF.

"  process the tables
LOOP AT IT_ALV.
l_index = sy-tabix.
READ TABLE ITAB WITH KEY QMNUM = IT_ALV-QMNUM.
IF SY-SUBRC EQ 0.
IT_ALV-QMTXT =  iTAB-QMTXT.
IT_ALV-ZZPOTSOL =  iTAB-ZZPOTSOL.
IT_ALV-OBJNR =  iTAB-OBJNR.
MODiFY ITAB INDEX l_index.
ENDIF.
ENDLOOP.

Re: Inner join 3 tables

Former Member — Wed, 04 Mar 2009 18:13:09 GMT

try to avoid using INTO CORRESPONDINg FIELDS.

You can do this by making sure that your fields are fetched in the same way as declared in ur ITAB.

And rest of the code as given by avinash seems to be good.

Thanks

Kiran

Re: Inner join 3 tables

Former Member — Wed, 04 Mar 2009 18:22:20 GMT


> try to avoid using INTO CORRESPONDINg FIELDS.
> 
> You can do this by making sure that your fields are fetched in the same way as declared in ur ITAB.
> 
> And rest of the code as given by avinash seems to be good.
> 
> Thanks
> Kiran

Kiran - I really don't see how this helps the OP.

Rob

Re: Inner join 3 tables

sarbajitm — Thu, 05 Mar 2009 04:19:12 GMT

Hi Avinash,

Your code is ok but Sorting IT_ALV using key QMNUM before looping at it help to increase

performance.I mean

Sort IT_ALV by OMNUM.

just put this line of code above LOOP AT IT_ALV statement

Re: Inner join 3 tables

Sathya_Gunasekaran — Thu, 05 Mar 2009 04:44:27 GMT

Hi Ana

I would suggest you to go for populating three different internal tables...and at the end populate the final ITAB.

Eg, select TPLNR PLTXT from IFLS_ADDR into table itab_ifls_addr.

select QMNUM from ZDRMIFLOT001 into table itab_ZDRMIFLOT001 for all entries in itab_ifls_Addr

where tplnr = itab_ifls_addr -tplnr.

select QMTXT ZZPOTSOL OBJNR from QMEL into table itab_QMEL

for all entries in itab_ZDRMIFLOT001 where QMNUM = itab_ZDRMIFLOT001-QNUM.

Finally populate Ur final ITAB using these three internal tables.

Hope this helps.

Thanks and regards

Sathya

Re: Inner join 3 tables

Former Member — Thu, 05 Mar 2009 09:08:53 GMT

Hi kiran dasari,

If I don't use

INTO CORRESPONDING FIELDS OF TABLE

the inner join doesn't work.

Thanks for your answer.

Re: Inner join 3 tables

Former Member — Thu, 05 Mar 2009 09:11:24 GMT

Hi Ramiro Escamilla,

I try it with left outer join

SELECT IFLS_ADDR~TPLNR  IFLS_ADDR~PLTXT
         ZDRMIFLOT001~QMNUM QMEL~QMTXT QMEL~ZZPOTSOL QMEL~OBJNR
  INTO CORRESPONDING FIELDS OF TABLE IT_ALV  FROM
  ZDRMIFLOT001 INNER JOIN IFLS_ADDR
            ON ZDRMIFLOT001~TPLNR = IFLS_ADDR~TPLNR
            LEFT OUTER JOIN QMEL
            ON QMEL~QMNUM = ZDRMIFLOT001~QMNUM
  WHERE IFLS_ADDR~TPLNR IN S_TPLNR
       AND   IFLS_ADDR~FLTYP IN TIPO
       AND   IFLS_ADDR~POST_CODE1 IN P_CODE1
       AND   IFLS_ADDR~CITY1 IN P_CITY1
       AND   IFLS_ADDR~STREET IN P_STREET.

but I have the same problem when QNUM = '0' . I loose the register.

Thanks for your answer.

Best regards.

Re: Inner join 3 tables

Former Member — Thu, 05 Mar 2009 09:24:45 GMT

Hi Avinash Kodarapu ,

I have decided to use a similar code of yours.Finally I can't use inner join because I need some fields = 0 and if I use inner join I loose this registers.

TYPES: BEGIN OF S_ALV,
       TPLNR TYPE IFLO-TPLNR,
       PLTXT TYPE IFLO-PLTXT,
       QMNUM TYPE QMEL-QMNUM, 
       QMTXT TYPE QMEL-QMTXT, 
       STAT  TYPE JEST-STAT,
       TXT30 LIKE  TJ02T-TXT30, 
       ZZPOTSOL TYPE QMEL-ZZPOTSOL, 
       OBJ TYPE QMEL-OBJNR, 
       MARC TYPE C, 
       END OF S_ALV.
TYPES: BEGIN OF S_AUX,
       TPLNR TYPE IFLO-TPLNR,
       PLTXT TYPE IFLO-PLTXT,
       END OF S_AUX.

DATA: IT_ALV TYPE STANDARD TABLE OF S_ALV ,
      IT_AUX TYPE STANDARD TABLE OF S_AUX WITH HEADER LINE,
      WA_ALV LIKE LINE OF IT_ALV.

SELECT TPLNR PLTXT
              INTO CORRESPONDING FIELDS OF TABLE IT_AUX
               FROM IFLS_ADDR WHERE TPLNR IN S_TPLNR
                              AND   FLTYP IN TIPO
                              AND   POST_CODE1 IN P_CODE1
                              AND   CITY1 IN P_CITY1
                              AND   STREET IN P_STREET.

  LOOP AT IT_AUX .
    WA_ALV-TPLNR = IT_AUX-TPLNR.
    WA_ALV-PLTXT = IT_AUX-PLTXT.
    SELECT  QMNUM INTO WA_ALV-QMNUM
            FROM ZDRMIFLOT001 WHERE TPLNR = IT_AUX-TPLNR.
      IF SY-SUBRC EQ ZERO.
        SELECT  SINGLE QMTXT ZZPOTSOL OBJNR
                               INTO (WA_ALV-QMTXT, WA_ALV-ZZPOTSOL,WA_ALV-OBJ)
                               FROM QMEL WHERE QMNUM = WA_ALV-QMNUM.
      ENDIF.
      INSERT WA_ALV INTO TABLE IT_ALV.
    ENDSELECT.
    IF SY-SUBRC NE ZERO.
      INSERT WA_ALV INTO TABLE IT_ALV.
    ENDIF.
    CLEAR: WA_ALV.
  ENDLOOP.

If somebody has a better solution ...

Thanks to all.

Re: Inner join 3 tables

Former Member — Thu, 05 Mar 2009 09:30:11 GMT

Hi,

I would like suggest to you go For all entries quiry instad inner join. Performence wise.

Regards

Md.MahaboobKhan

Re: Inner join 3 tables

keerthy_k — Thu, 05 Mar 2009 09:40:44 GMT

Hi Ana,

please se the code below:

DATA: IT_IFLS_ADDR TYPE TABLE OF IFLS_ADDR,

IT_ZDRMIFLOT001 TYPE TABLE OF ZDRMIFLOT001,

IT_QMEL TYPE TABLE OF QMEL.

SELECT TPLNR

PLTXT FROM IFLS_ADDR

INTO CORRESPONDING FIELDS OF TABLE IT_IFLS_ADDR

WHERE TPLNR IN S_TPLNR

AND FLTYP IN TIPO

AND POST_CODE1 IN P_CODE1

AND CITY1 IN P_CITY1

AND STREET IN P_STREET.

IF IT_IFLS_ADDR IS NOT INITAIL.

SELECT QMNUM

FROM ZDRMIFLOT001

INTO CORRESPONDING FIELDS OF TABLE IT_ZDRMIFLOT001

FOR ALL ENTRIES IN IT_IFLS_ADDR

WHERE TPLNR = IT_IFLS_ADDR-TPLNR

ENDIF.

IF IT_ZDRMIFLOT001 IS NOT INITAIL.

SELECT QMTXT

ZZPOTSOL

OBJNR

FROM QMEL

INTO CORRESPONDING FIELDS OF TABLE IT_QMEL

FOR ALL ENTRIES IN IT_ZDRMIFLOT001

WHERE QMNUM = IT_ZDRMIFLOT001-QMNUM

ENDIF.

Hope this will help u.

Keerthi

Edited by: Keerthy K on Mar 5, 2009 10:41 AM

Re: Inner join 3 tables

Former Member — Thu, 05 Mar 2009 15:25:08 GMT

Thanks to all.

Ana

Re: Inner join 3 tables

Former Member — Thu, 05 Mar 2009 15:33:47 GMT


> I would like suggest to you go For all entries quiry instad inner join. Performence wise.

Usually not correct.

Rob