topic innerjoin in Application Development and Automation Discussions

innerjoin

Former Member — Fri, 16 Sep 2005 09:33:29 GMT

can anyone can tell me how to join 4 tables and correct the below coding..

select mara_{matnr vbap}arktx likp_{vbeln vbap}netpr

likp_{waerk vbap}audat likp_{netwr mara}mstae

vbak_{auart vbak}vtweg vbap_{posnr vbap}shkzg mara~extwg

mara_{mstav likp}lfdat likp_{lfart likp}wadat_ist

likp_{vkorg mara}kunnr mara~matkl into corresponding

fields of table itab from vbak

inner join likp

on vbak_{vbeln = likp}vbeln

inner join vbap

on vbak_{vbeln = vbap}vbeln

inner join mara

on vbap_{matnr = mara}matnr

inner join vbak
on lips_{vbeln = vbak}vbeln

where vbak-vtweg in s_vtweg

and mara-kunnr in s_kunnr

and vbap-audat in s_audat

and vbak-auart in s_auart

and likp-vkorg in s_vkorg

and vbap-netpr in s_netpr.

thks in advance

raju.

Re: innerjoin

Former Member — Fri, 16 Sep 2005 09:42:47 GMT

i dont see any problmes in this select.

minor observation.

It should be

where vbak~vtweg in s_vtweg

and mara~kunnr in s_kunnr

and vbap~audat in s_audat

and vbak~auart in s_auart

and likp~vkorg in s_vkorg

and vbap~netpr in s_netpr.

instead of

where vbak-vtweg in s_vtweg

and mara-kunnr in s_kunnr

and vbap-audat in s_audat

and vbak-auart in s_auart

and likp-vkorg in s_vkorg

and vbap-netpr in s_netpr.

Re: innerjoin

Former Member — Fri, 16 Sep 2005 09:47:56 GMT

hi,

select a_{vbeln b}arktx c_{netwr d}matnr into itab

from vbak as a

inner join vbap as b on a_{vbeln = b}vbeln

inner join likp as c on a_{vbeln = b}vbeln

inner join mara as d on b_{matnr = d}matnr

where …………..include all the conditions

cheers,

sasi

Re: innerjoin

Former Member — Fri, 16 Sep 2005 09:52:48 GMT

hi,

write where condition as per the key fields like

where vbak-vtweg in s_vtweg and

vbak-vkorg in s_vkorg and

vbak-auart in s_auart and

vbak-kunnr in s_kunnr and

vbap-audat in s_audat and

vbap-netpr in s_netpr.

Re: innerjoin

Former Member — Fri, 16 Sep 2005 11:17:31 GMT

i wrote like this but it is not showing me any error ,..but no result at end...pls correct it

REPORT YKADLGTB .

INTERNAL TABLES *****************

***********************************************************************

TABLES : MARA, VBAP, VBAK, LIKP.

**********************************DATA********************************

************************************************************************

data: begin of itab occurs 0,

matnr type mara-matnr,

matkl type mara-matkl,

vbeln type likp-vbeln,

netpr type vbap-netpr,

posnr type vbap-posnr,

shkzg type vbap-shkzg,

extwg type mara-extwg,

mstae type mara-mstae,

mstav type mara-mstav,

lfdat type likp-lfdat,

lfart type likp-lfart,

kunnr type likp-kunnr,

wadat_ist type likp-wadat_ist,

end of itab.

**********************SELECTION SCEREEN*******************************

************************************************************************

selection-screen begin of block b1 with frame title text-010.

SCREEN-INTENSIFIED = '1'.

select-options:

s_audat for vbap-audat no-extension , "Document Date.

s_kunnr for vbak-kunnr no-extension. " DEFAULT '5525' .

selection-screen begin of block b2 with frame title text-020.

select-options:

s_auart for vbak-auart no-extension no intervals,

s_vtweg for vbak-vtweg no-extension no intervals,

s_vkorg for vbak-vkorg no-extension no intervals,

s_netpr for vbap-netpr no-extension no intervals no-display.

selection-screen end of block b2.

selection-screen end of block b1.

selection-screen begin of block b3 with frame title text-030.

select-options:

*s_kunnr for likp-kunnr no-extension MODIF ID SC1,

*"CUSTOMER DEFAULT '5525' .

s_matnr for mara-matnr no-extension, "MATERIAL NUMBER

s_matkl for mara-matkl no-extension no intervals, "MATERIAL TYPE.

s_extwg for mara-extwg no-extension no intervals. "MATERIAL GROUP.

selection-screen begin of block b4 with frame title text-040.

select-options:

s_lfdat for likp-lfdat no-extension , "DELIVERY DATE.

s_shkzg for vbap-shkzg no-extension no intervals MODIF ID SC1,"RETURNS .

s_lfart for likp-lfart no-extension no intervals, "DELIVERY TYPE.

s_mstae for mara-mstae no-extension no intervals,

s_mstav for mara-mstav no-extension no intervals.

selection-screen end of block b4.

selection-screen end of block b3.

select likp_{vbeln vbap}netpr vbap~posnr

vbap_{shkzg mara}extwg mara_{mstae mara}matnr

mara_{mstav likp}lfdat likp_{lfart mara}matkl likp~kunnr

likp~wadat_ist into corresponding

fields of table itab from likp

inner join vbak on

likp_{vbeln = vbak}vbeln

inner join vbap on

vbak_{vbeln = vbap}vbeln

inner join mara on

vbap_{matnr = mara}matnr

where vbap~shkzg in s_shkzg

and mara~matkl in s_matkl

and mara~kunnr in s_kunnr

and likp~lfdat in s_lfdat

and likp~lfart in s_lfart

and mara~matnr in s_matnr

and mara~extwg in s_extwg

and mara~mstae in s_mstae

and mara~mstav in s_mstav.

loop at itab.

format color 2.

if sy-subrc = 0.

uline (142).

write: / sy-vline, itab-matnr, 12 itab-matkl , 20 itab-vbeln,

28 itab-netpr, 75 itab-posnr,86 itab-shkzg,

108 itab-extwg, 125 itab-mstae,134 itab-mstav,

45 itab-lfdat, 58 itab-lfart, 66 itab-kunnr,

94 itab-wadat_ist, 142 sy-vline.

endif.

format color off.

endloop.

uline (142).

raju....

Re: innerjoin

Former Member — Fri, 16 Sep 2005 11:28:17 GMT

Your inner join looks wrong

inner join vbak on

likp_{vbeln = vbak}vbeln

as these won't be the same if the delivery is created from a sales order. The sales order number/item is held on LIPS. You will need to read LIKP then LIPS and back to the sames order.

Re: innerjoin

Former Member — Fri, 16 Sep 2005 11:34:03 GMT

Raju,

I think you should learn the inner join concept in detail as the coding was not at all correct. In the forum, the to ask things in which you have some issues, rather then asking people to CODE FOR YOU.

Anyways look at the following example which will help you in future while working on iner joins for multiple tables. If you code this way, you will never ever find problems in inner joins-

DATA: BEGIN OF WA,

FLIGHT TYPE SFLIGHT,

PFLI TYPE SPFLI,

CARR TYPE SCARR,

END OF WA.

SELECT * INTO WA

FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID )

INNER JOIN SCARR AS C

ON F_{CARRID = C}CARRID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID,

WA-FLIGHT-CONNID.

ENDSELECT.

It will be better for your future if you make use of this forum to get your queries resolved where you are stuck rather than asking people on forums to code for you. In this way , you will not be learning and i hope you definetely eant to learn.

If this resolves your query then kindly close the thread and reward points

Regards.

Naveen

Re: innerjoin

Former Member — Fri, 16 Sep 2005 11:54:37 GMT

hi,

vbak-vbeln <> likp-vbeln

vbak-vbeln is sales document

likp-vbeln is delivery document

so you need to find out delivery number to the sales document that can be possible VBFA table

inner join vbak on

select likp_{vbeln vbap}netpr vbap~posnr

vbap_{shkzg mara}extwg mara_{mstae mara}matnr

mara_{mstav likp}lfdat likp_{lfart mara}matkl likp~kunnr

likp~wadat_ist into corresponding

fields of table itab from vbak

inner join vbfa on

vbak_{vbeln = vbfa}vbelv

inner join likp on

likp-vbeln = vbfa-vbeln

inner join vbap on

vbak_{vbeln = vbap}vbeln

inner join mara on

vbap_{matnr = mara}matnr

in the where condition you add

vbfa-VBTYP_N = 'J' ( delivery )

hope this will work

Cheers,

Sasi