topic Re: selecting from 3tables in Application Development and Automation Discussions

selecting from 3tables

Former Member — Mon, 09 Jun 2008 08:07:21 GMT

can anyone provide me a sample select statement to fetch data from 3 different tables pls..

its urgent

Re: selecting from 3tables

Former Member — Mon, 09 Jun 2008 08:11:19 GMT

Hi ,

Here is code of join of 4 tables

SELECT lips~vgbel "Document number of the reference document

lips~vgpos "Item number of the reference item

lips~matnr "Material

lips~charg "Batch

lips~lfimg " Delivery quantity

likp~wadat_ist "Actual goods movement date

likp~vstel "Shipping Point/Receiving Point

mara~zztrade_name " Trade Name

INTO CORRESPONDING FIELDS OF TABLE rt_delivery_ord

FROM likp

INNER JOIN lips ON lips_{vbeln EQ likp}vbeln

INNER JOIN vbap ON vbap_{vbeln EQ lips}vgbel

AND vbap_{posnr EQ lips}vgpos

INNER JOIN mara ON mara_{matnr EQ vbap}matnr

FOR ALL entries IN rt_sales_ord

WHERE wadat_ist IN rt_date "Goods Issue Date

AND likp~vstel IN rt_shipping "Shipping Point

AND mara~mtart IN o_mtart "Material Type

AND mara~zztrade_name IN o_trade "Trade Name

AND mara~zzmanu_stg IN rt_stage. "Manuf Stage

AND lips~vgbel EQ rt_sales_ord-vbeln "Sales Order
AND lips~posnr EQ rt_sales_ord-posnr "Sales item
AND lips~charg EQ rt_sales_ord-charg. "Batch

Please reward if useful.

Re: selecting from 3tables

Former Member — Mon, 09 Jun 2008 08:16:25 GMT

Select *

from table1 as a

into t_table

inner join table2 as b

on a_{fieldname = b}fieldname

inner join table3 c

on a_{fieldname = c}fieldname

where a~fieldname = g_variable1

and b~fieldname = g_variable2.

FORM F_GET_ALL_PURCHASE_ORDERS TABLES P_PO STRUCTURE INT_PO.

SELECT L_{EBELN L}EBELP M_{EINDT R}AUFNR R_{AUFPL_ORD R}APLZL_ORD

INTO TABLE INT_PO

FROM EKPO AS L LEFT JOIN EKET AS M

ON ( L_{EBELN = M}EBELN AND

L_{EBELP = M}EBELP )

INNER JOIN EKKN AS R

ON ( L_{EBELN = R}EBELN AND

L_{EBELP = R}EBELP )

WHERE L~LOEKZ = '' AND "Deletion flag

L~STATU = 'F' AND "RFQ status F created via Prod Ord

L~WERKS = '0008' AND "Plant

l~elikz = '' AND "Delivery completed indicator

L~BSTYP = 'F'. "Purch document category F = Purch Ord

ENDFORM. " F_GET_ALL_PURCHASE_ORDERS

Reward if useful

Re: selecting from 3tables

Former Member — Mon, 09 Jun 2008 08:23:25 GMT

Hi,

Please find below code for joining AUFK, AFKO and MAKT Tables.

select a~aufnr

a~auart

a~autyp

a~refnr

a~ernam

a~erdat

a~ktext

a~werks

b~gltrp

b~gstrp

b~gamng

b~gmein

b~plnbez

b~stlty

b~stlnr

b~stlan

b~stlal

c~maktx

into (wa_aufk_afko-aufnr,

wa_aufk_afko-auart,

wa_aufk_afko-autyp,

wa_aufk_afko-refnr,

wa_aufk_afko-ernam,

wa_aufk_afko-erdat,

wa_aufk_afko-ktext,

wa_aufk_afko-werks,

wa_aufk_afko-gltrp,

wa_aufk_afko-gstrp,

wa_aufk_afko-gamng,

wa_aufk_afko-gmein,

wa_aufk_afko-plnbez,

wa_aufk_afko-stlty,

wa_aufk_afko-stlnr,

wa_aufk_afko-stlan,

wa_aufk_afko-stlal,

wa_aufk_afko-maktx)

from aufk

as a inner join afko as b

on a_{aufnr = b}aufnr

join makt as c

on b_{plnbez = c}matnr and

c~spras = sy-langu

where a~auart = c_auart and

b~stlty = c_stlty and

a~aufnr in s_aufnr.

append wa_aufk_afko to t_aufk_afko.

clear wa_aufk_afko.

endselect.

Regards,

Deepa Kulakrni

Re: selecting from 3tables

Former Member — Mon, 09 Jun 2008 08:24:05 GMT

Hi,

Please find below code for joining AUFK, AFKO and MAKT Tables.

select a~aufnr

a~auart

a~autyp

a~refnr

a~ernam

a~erdat

a~ktext

a~werks

b~gltrp

b~gstrp

b~gamng

b~gmein

b~plnbez

b~stlty

b~stlnr

b~stlan

b~stlal

c~maktx

into (wa_aufk_afko-aufnr,

wa_aufk_afko-auart,

wa_aufk_afko-autyp,

wa_aufk_afko-refnr,

wa_aufk_afko-ernam,

wa_aufk_afko-erdat,

wa_aufk_afko-ktext,

wa_aufk_afko-werks,

wa_aufk_afko-gltrp,

wa_aufk_afko-gstrp,

wa_aufk_afko-gamng,

wa_aufk_afko-gmein,

wa_aufk_afko-plnbez,

wa_aufk_afko-stlty,

wa_aufk_afko-stlnr,

wa_aufk_afko-stlan,

wa_aufk_afko-stlal,

wa_aufk_afko-maktx)

from aufk

as a inner join afko as b

on a_{aufnr = b}aufnr

join makt as c

on b_{plnbez = c}matnr and

c~spras = sy-langu

where a~auart = c_auart and

b~stlty = c_stlty and

a~aufnr in s_aufnr.

append wa_aufk_afko to t_aufk_afko.

clear wa_aufk_afko.

endselect.

Regards,

Deepa Kulakrni

Re: selecting from 3tables

Former Member — Mon, 09 Jun 2008 08:25:22 GMT

hi,

Check out the bwlow sample 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.

Regards,

Santosh