09-16-2005 10:33 AM
can anyone can tell me how to join 4 tables and correct the below coding..
select maramatnr vbaparktx likpvbeln vbapnetpr
likpwaerk vbapaudat likpnetwr maramstae
vbakauart vbakvtweg vbapposnr vbapshkzg mara~extwg
maramstav likplfdat likplfart likpwadat_ist
likpvkorg marakunnr mara~matkl into corresponding
fields of table itab from vbak
inner join likp
on vbakvbeln = likpvbeln
inner join vbap
on vbakvbeln = vbapvbeln
inner join mara
on vbapmatnr = maramatnr
inner join vbak
on lipsvbeln = vbakvbeln
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.
09-16-2005 10:42 AM
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.
09-16-2005 10:42 AM
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.
09-16-2005 10:47 AM
hi,
select avbeln barktx cnetwr dmatnr into itab
from vbak as a
inner join vbap as b on avbeln = bvbeln
inner join likp as c on avbeln = bvbeln
inner join mara as d on bmatnr = dmatnr
where ..include all the conditions
cheers,
sasi
09-16-2005 10:52 AM
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.
09-16-2005 12:17 PM
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 likpvbeln vbapnetpr vbap~posnr
vbapshkzg maraextwg maramstae maramatnr
maramstav likplfdat likplfart maramatkl likp~kunnr
likp~wadat_ist into corresponding
fields of table itab from likp
inner join vbak on
likpvbeln = vbakvbeln
inner join vbap on
vbakvbeln = vbapvbeln
inner join mara on
vbapmatnr = maramatnr
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....
09-16-2005 12:28 PM
Your inner join looks wrong
inner join vbak on
likpvbeln = vbakvbeln
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.
09-16-2005 12:54 PM
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 likpvbeln vbapnetpr vbap~posnr
vbapshkzg maraextwg maramstae maramatnr
maramstav likplfdat likplfart maramatkl likp~kunnr
likp~wadat_ist into corresponding
fields of table itab from vbak
inner join vbfa on
vbakvbeln = vbfavbelv
inner join likp on
likp-vbeln = vbfa-vbeln
inner join vbap on
vbakvbeln = vbapvbeln
inner join mara on
vbapmatnr = maramatnr
in the where condition you add
vbfa-VBTYP_N = 'J' ( delivery )
hope this will work
Cheers,
Sasi
09-16-2005 12:34 PM
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 FCARRID = PCARRID AND
FCONNID = PCONNID )
INNER JOIN SCARR AS C
ON FCARRID = CCARRID
WHERE P~CITYFROM = 'FRANKFURT'
AND P~CITYTO = 'NEW YORK'
AND F~FLDATE BETWEEN '20010910' AND '20010920'
AND FSEATSOCC < FSEATSMAX.
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