topic HOW DEFINE INNER JIONS FOR MULTIPLE TABLE-FIELDS in Application Development and Automation Discussions

HOW DEFINE INNER JIONS FOR MULTIPLE TABLE-FIELDS

Former Member — Wed, 12 Mar 2008 03:36:23 GMT

HI I HAVE THE IMMEDIATE ISSUE TO RESOLVE...

data: begin of it_jtab occurs 0,

gsber like tgsb-gsber,

gjahr like coss-gjahr,

kostl like csks-kostl,

posid like prps-posid,

plfaz like proj-plfaz,

plsez like proj-plsez,

estat like tj30t-estat,

perbl like coss-perbl,

wkgbtr like coep-wkgbtr,

end of it_jtab.

HOW CAN I DO INNER JOIN FOR THE GIVEN TABLE??

AM CURENTLY ON 4.0 VERSION HENCE HELP ME KNOW TO WORK ON JOINS ONLY.........

Re: HOW DEFINE INNER JIONS FOR MULTIPLE TABLE-FIELDS

Former Member — Wed, 12 Mar 2008 03:38:41 GMT

Inner joins using 3 tables

Try this :-


SELECT stpo~stlnr stpo~idnrk mast~matnr mara~mtart stpo~menge 
INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast 
JOIN stpo ON stpo~stlnr = mast~stlnr 
JOIN mara ON mara~matnr = mast~matnr 
WHERE stpo~stlty = 'M' "AND stpo~idnrk IN s_matnr 
AND mast~werks = 1000.

Here s_matnr is a select-options on the selection-screen.

Or this.


     Select single Vbrk~Bukrs Vbrk~Kunrg    Vbrk~Vbeln 
                   Vbrk~Fkdat Vbrk~Bstnk_Vf Vbrk~Zterm 
                   Tvzbt~Vtext 
                   Vbak~Vbeln Vbak~Bstdk 
                   Likp~Vbeln Likp~lfdat    Likp~Lfuhr 
       into w_vbrk 
       from vbrk 
      inner join       Tvzbt on Tvzbt~Zterm        = Vbrk~Zterm      and 
                                Tvzbt~Spras        = sy-langu 
      Inner join       Vbfa  as SalesLnk 
                             on SalesLnk~vbeln     = pu_vbeln        and 
                                SalesLnk~vbtyp_v   = c_order 
            inner join Vbak  on Vbak~Vbeln           = SalesLnk~Vbelv
      Inner join       Vbfa  as DeliveryLnk 
                             on DeliveryLnk~vbeln   = pu_vbeln       and 
                                DeliveryLnk~vbtyp_v = c_Delivery 
            inner join Likp  on Likp~Vbeln          = DeliveryLnk~Vbelv 
      where vbrk~vbeln = pu_Vbeln.

This code locates sales, delivery and payment terms info from a billing document number.

Here, this one also works fine :


select zfpcd~cadivi zfpcd~proforma zfpcd~factura zfpcd~aniofactura 
zfpcd~montousd zfpcd~montoap zfpcd~ebeln zfpcd~inco1 
zfpcd~lifnr lfa1~name1 zcdvs~status zfpcd~conint 
into it_lista 
from zfpcd inner join zcdvs 
on zfpcd~ebeln = zcdvs~ebeln 
and zfpcd~proforma = zcdvs~proforma 
and zfpcd~lifnr = zcdvs~lifnr 
inner join lfa1 
on zfpcd~lifnr = lfa1~lifnr 
where zcdvs~status = '04'.

Re: HOW DEFINE INNER JIONS FOR MULTIPLE TABLE-FIELDS

Former Member — Wed, 12 Mar 2008 04:15:45 GMT

Hi,

U can join 3 tables max. Joining more than 3 tables may cause performance issues.

select a_{abc b}def c~ghi

into table itab

from a join b

on a_{jkl = b}mno

join c

on a_{pqr = c}stu

where <condition>.

Make sure that u use index keys (primary keys) while joining two tables. Else u will get performance error.

Reward if helpful.

Regards,

Ramya

Re: HOW DEFINE INNER JIONS FOR MULTIPLE TABLE-FIELDS

Former Member — Wed, 12 Mar 2008 04:35:33 GMT

Hi,

Check this prog,

Inner joins using 3 tables

Try this :-

SELECT stpo_{stlnr stpo}idnrk mast_{matnr mara}mtart stpo~menge

INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast

JOIN stpo ON stpo_{stlnr = mast}stlnr

JOIN mara ON mara_{matnr = mast}matnr

WHERE stpo_{stlty = 'M' "AND stpo}idnrk IN s_matnr

AND mast~werks = 1000.

Here s_matnr is a select-options on the selection-screen.

Or this.

Code:

Select single Vbrk_{Bukrs Vbrk}Kunrg Vbrk~Vbeln

Vbrk_{Fkdat Vbrk}Bstnk_Vf Vbrk~Zterm

Tvzbt~Vtext

Vbak_{Vbeln Vbak}Bstdk

Likp_{Vbeln Likp}lfdat Likp~Lfuhr

into w_vbrk

from vbrk

inner join Tvzbt on Tvzbt_{Zterm = Vbrk}Zterm and

Tvzbt~Spras = sy-langu

Inner join Vbfa as SalesLnk

on SalesLnk~vbeln = pu_vbeln and

SalesLnk~vbtyp_v = c_order

inner join Vbak on Vbak_{Vbeln = SalesLnk}Vbelv

Inner join Vbfa as DeliveryLnk

on DeliveryLnk~vbeln = pu_vbeln and

DeliveryLnk~vbtyp_v = c_Delivery

inner join Likp on Likp_{Vbeln = DeliveryLnk}Vbelv

where vbrk~vbeln = pu_Vbeln.

This code locates sales, delivery and payment terms info from a billing document number.

Here, this one also works fine :

select zfpcd_{cadivi zfpcd}proforma zfpcd_{factura zfpcd}aniofactura

zfpcd_{montousd zfpcd}montoap zfpcd_{ebeln zfpcd}inco1

zfpcd_{lifnr lfa1}name1 zcdvs_{status zfpcd}conint

into it_lista

from zfpcd inner join zcdvs

on zfpcd_{ebeln = zcdvs}ebeln

and zfpcd_{proforma = zcdvs}proforma

and zfpcd_{lifnr = zcdvs}lifnr

inner join lfa1

on zfpcd_{lifnr = lfa1}lifnr

where zcdvs~status = '04'.

Regards,

Arunsri