How to create internal table from 3 different tables

[Former Member]

hi all,

I have 3 tables in 2 tables there is common field vendor_no

and there is other field exchange i.e in 3rd table called currency this field contains all exchange rates of different currencies.

how to write select statement using different fields from different tables.

i have vendor_no in table 1 and table 2,

amt details paid in different currencies to these vendors in table 2.

now i want to get all exchange rates of USD (equalivent to USD) from table 3 according to the amount paid to vendors and the total amt in USD.

for example:

ven_no--cur-amt--exch(equ to 1 USD)--total_in_usd

111-----AUD-100---1,29--

---

77,51

111-----yen-1000--109,41--

---

9,1

kindly help me regarding this if possible with example.

Thanks in advance,

Regards,

Venu.

[Former Member]

hi all,

I have 3 tables in 2 tables there is common field vendor_no

and there is other field exchange i.e in 3rd table called currency this field contains all exchange rates of different currencies.

how to write select statement using different fields from different tables.

i have vendor_no in table 1 and table 2,

amt details paid in different currencies to these vendors in table 2.

now i want to get all exchange rates of USD (equalivent to USD) from table 3 according to the amount paid to vendors and the total amt in USD.

for example:

ven_no--cur-amt--exch(equ to 1 USD)--total_in_usd

111-----AUD-100---1,29--

---

77,51

111-----yen-1000--109,41--

---

9,1

kindly help me regarding this if possible with example.

Thanks in advance,

Regards,

Venu.

[Former Member]

Hi,

One soloution would be to try and get all the data from the 3 tables into one single table.

declare itab4 with all the relevant fields from itab1, itab2 and itab3.

Loop at itab1.

read table itab2 with key vendor_no = itab1-vendor_no.

read table itab3 with key currency = itab2-currency.

Now populate the values of itab1, itab2, itab3 into a table itab4.

Endloop.

Now u can do a select using option "FOR ALL ENTRIES IN ITAB4" and use appropriate logic in where condition.

[Former Member]

Hi,

One way to populate an internal table with data from 3 database tables is by using join statement. Like see the example code below, which fetches sales order details from date1 to date2 from tables vbak,vbap,kna1 & makt.

SELECT vbak~vbeln

vbak~erdat

vbak~kunnr

kna1~name1

vbap~matnr

makt~maktx

vbap~kwmeng

vbap~netpr

vbak~netwr

INTO table itab FROM ( ( vbak INNER JOIN kna1

ON vbak_{kunnr = kna1}kunnr ) INNER JOIN vbap

ON vbak_{vbeln = vbap}vbeln ) INNER JOIN makt

ON vbap_{matnr = makt}matnr AND makt~spras = 'EN'

WHERE ( vbak_{erdat GE date1 AND vbak}erdat LE date2 ).

This may help.For further problems u can revert back.

Thanks & Regds,

Ravikiran.

[Former Member]

Hi Ravi,

Thanks for replying.

i tried in this way it is showing error.

data : begin of wa occurs 0,

bukrs type bsak-bukrs,

lifnr type bsak-lifnr,

land1 type lfa1-land1,

name1 like lfa1-name1,

waers like bsak-waers,

ukurs like tcurr-ukurs,

total type i value 0,

count type i value 0.

data :end of wa.

select bsak_{bukrs bsak}lifnr lfa1_{name1 bsak}waers lfa1_{land1 tcurr}ukurs into corresponding fields of

table wa from ((bsak inner join lfa1 on bsak_{lifnr = lfa1}lifnr)inner join wa on tcurr_{fcurr = bsak}waers)

where bsak~bukrs in s_bukrs.

kindly help me regarding this.

regards,

venu.

[andreas_mann3]

Hi Venu ,

take that code:

SELECT bsak~bukrs bsak~lifnr lfa1~name1 bsak~waers lfa1~land1
tcurr~ukurs INTO CORRESPONDING FIELDS OF TABLE wa
FROM ( bsak AS bsak INNER JOIN lfa1 AS lfa1 ON bsak~lifnr = lfa1~lifnr )
JOIN tcurr AS tcurr  ON tcurr~fcurr = bsak~waers
WHERE bsak~bukrs IN s_bukrs.

memo : i don't know your query.

but when you'll sum the amounts in local currency -

just sum field bsak-dmbtr !

regards Andreas

[jayanthi_jayaraman]

Hi,

Proper spacing brackets are necessary.

select bsak_{bukrs bsak}lifnr lfa1_{name1 bsak}waers lfa1~land1

tcurr~ukurs into corresponding fields of

table wa from ( ( bsak inner join lfa1 on bsak_{lifnr = lfa1}lifnr ) <b>inner join tcurr</b> on tcurr_{fcurr = bsak}waers )

where bsak~bukrs in s_bukrs.

[Former Member]

hi all,

i am able to get data from 2 different tables how about from 3rd table in which exchange rates will be available, i need from the thrid table tcurr from the where field fcurr = EUR.

can anybody look into this and help me out if possible with example.

thanks in advance.

regards,

venu.

[andreas_mann3]

Hi,

for what you need to select table tcurr

-i've never done or heard in that context!

Why do'nt you select : where bsak-waers = 'EUR' ?

regards Andreas

[Former Member]

Hi,

actually i need to display the data in to the grid.

i am able to display certain columns like vendor_no, amt_paid to vendor, currency......

like that

i need to get exchange rate equalivent to EUR

if amt paid to vendor is in USD then in the exchange column i need to get 1 EUR eq how much USD....

to get this i am using table tcurr.

kindly help me is there any other alternative.

thanks in advance

regards,

venu.

[Former Member]

Hi venu,

Try this code, it should work.

select bsak~bukrs

bsak~lifnr

lfa1~name1

bsak~waers

lfa1~land1

tcurr~ukurs

into corresponding fields of table wa from ( ( bsak inner join lfa1 on bsak_{lifnr = lfa1}lifnr )

inner join tcurr on tcurr_{fcurr = bsak}waers and tcurr~fcurr = 'eur' )

where bsak~bukrs in s_bukrs.

Thanks and Regds,

Ravikiran.

[Former Member]

Hi,

if i am using this code then it is not displaying the data in the grid.

so is there any other alternative other than inner join.

if so kindly provide me with example.

regards,

venu.

[Former Member]

Hi venu,

Probably the other way is to create a database view. But in many cases it is not a better solution as far as I know.

Thanks & Regds,

Ravikiran.

[jayanthi_jayaraman]

Hi,

select bsak_{bukrs bsak}lifnr lfa1_{name1 bsak}waers lfa1~land1

tcurr~ukurs into corresponding fields of

table wa from ( ( bsak inner join lfa1 on bsak_{lifnr = lfa1}lifnr ) inner join tcurr on tcurr_{fcurr = bsak}waers )

where bsak~bukrs in s_bukrs

and tcurr~fcurr = 'EUR'.

Kindly reward points for useful answers.

If you have any doubts , get back.