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

selection from 3 tables

Former Member — Fri, 11 Nov 2005 01:30:07 GMT

Hi,

I have select-options: s_matnr for mchb-matnr &

s_pradh for mara-pradh & s_wdatu for lqua-wdatu.

I want to select records from mchb(matnr,wekrs,lgort)

, mara(matnr,pradh) and lqua(matnr,wdatu)

can any one tell me how do i achieve this?

points guaranteed..

cheers

kaki

Re: selection from 3 tables

Former Member — Fri, 11 Nov 2005 01:34:47 GMT

select mara_{matnr, mara}pradh, mchb_{werks, mchb}lgort,lqua~wdatu into table itab

from mara

inner join on mchb_{matnr = mara}matnr

inner join on lqua_{matnr = mara}matnr

where mara~matnr in s_matnr and

mara~pradh in s_pradh and

lqua~wdatu in s_wdatu.

Cheers,

Satya

Re: selection from 3 tables

Former Member — Fri, 11 Nov 2005 01:49:50 GMT

Hi Kaki,

select mchb~matnr

mchb~werks

mchb~lgort

mara~pradh

lqua~wdatu

into table itab

from mchb

inner join mara on mara_{matnr = mchb}matnr

inner join lqua on lqua_{matnr = mchb}matnr and

lqua_{werks = mchb}werks

where mchb~matnr in s_matnr and

mara~pradh in s_pradh and

lqua~wdatu in s_wdatu.

Thanks.

Nethaji.

Re: selection from 3 tables

Former Member — Fri, 11 Nov 2005 01:58:28 GMT

Hi use Table joins:

select a~matnr

b~werks

b~lgort

a~pradh

c~wdatu

into table internal tab

from ( mara as a

join mchb as b on a_{matnr = b}matnr )

join lqua as c on c_{matnr = a}matnr and

c_{werks = b}werks

where a_{matnr in/FOR s_matnr/ p_PRADHA (if Parameter)and a}pradh in/for S_PRADH /P_PRADH(if parameter)

b~wdatu in/FOR S_wdatu.

Try to use material type it will retrive data faster.

Re: selection from 3 tables

Former Member — Fri, 11 Nov 2005 02:42:46 GMT

Hi Satya,

I want to make selections based on material receiving date(lqua-wdatu)along with mchb & mara.

cheers

kaki

Re: selection from 3 tables

Former Member — Fri, 11 Nov 2005 03:09:37 GMT

The following table join also looks into LQUA-WDATU.

This join first select material and prod. Hierarchy from MARA and check table MCHB to retrive WERKS and LGORT and

check against LQUA for WDATU.

Hi use Table joins:

select a~matnr

b~werks

b~lgort

a~pradh

c~wdatu

into table internal tab

from ( mara as a

join mchb as b on a_{matnr = b}matnr )

join lqua as c on c_{matnr = a}matnr and

c_{werks = b}werks

where a~matnr in S_MATNR AND

a~PRADHA in S_PRADH AND

b~wdatu in S_wdatu.

Try to use material type it will retrieve data faster.

Re: selection from 3 tables

RichHeilman — Fri, 11 Nov 2005 03:17:50 GMT

Looks like you are extending you select statement that you built earier. Here is some modify code from the previous post. Its pretty much like what has already been posted here.



report zrich_0001 .
 
 
tables: mchb, mara, lqua.
 
data: begin of itab occurs 0,
      matnr type mchb-matnr,
      werks type mchb-werks,
      lgort type mchb-lgort,
      pradh type mara-prdha,
      wdatu type lqua-wdatu,
      end of itab.
 
 
select-options: s_matnr for mchb-matnr,
                s_prdha for mara-prdha,
                s_wdatu for lqua-wdatu.
 
 
start-of-selection.
 
 
  select * into corresponding fields of table itab
            from mchb
                  inner join mara
                      on mchb~matnr = mara~matnr
                  inner join lqua
                      on mchb~matnr = lqua~matnr
                     and mchb~werks = lqua~werks
                              where mchb~matnr in s_matnr
                                and mara~prdha in s_prdha
                                and lqua~wdatu in s_wdatu.

Regards,

Rich Heilman

Re: selection from 3 tables

Former Member — Fri, 11 Nov 2005 03:37:36 GMT

Select a~matnr a~werks a~lgort b~pradh c~wdatu
       into corresponding fields of table itab
       from mchb as a join mara as b
       on a~matnr = b~matnr
       lqua as join c
       on a~matnr = c~matnr
       where a~matnr IN s_matnr
       and   b~pradh IN s_pradh
       and   c~WDATU IN s_wdatu.

Hope this helps u.

Re: selection from 3 tables

Former Member — Fri, 11 Nov 2005 04:03:46 GMT

select mara~matnr

mara~pradha

mchb~werks

mchb~lgort

lqua~wdatu

into table itab

from mara

inner join on mchb_{matnr = mara}matnr

inner join on lqua_{matnr = mara}matnr

where mara~matnr in s_matnr and

mara~pradha in s_pradh and

lqua~wdatu in s_wdatu.

Re: selection from 3 tables

jayanthi_jayaraman — Fri, 11 Nov 2005 04:13:26 GMT

Hi,

Since there is no common key fields in LQUA and other tables,I prefer to use for all entries for that.Here is the complete program.Kindly reward points if it helps you.

types : begin of ty1,

matnr type mara-matnr,

pradh type mara-pradh,

werks type mchb-werks,

lgort type mchb-lgort,

end of ty1.

types : begin of ty2,

matnr type lqua-matnr,

wdatu type lqua-wdatu,

end of ty2.

types : begin of ty,

matnr type mara-matnr,

pradh type mara-pradh,

werks type mchb-werks,

lgort type mchb-lgort,

wdatu type lqua-wdatu,

end of ty.

data : itab type standard table of ty,

itab1 type standard table of ty1,

itab2 type standard table of ty2,

wa type ty,

wa1 type ty1,

wa2 type ty2.

select mara_{matnr mara}pradh mchb_{werks mchb}lgort

into table itab1

from mara inner join mchb

on mara_{matnr = mchb}matnr

where mara~matnr in S_matnr

and mara~pradh in s_pradh.

select matnr wdatu from lqua into table itab2

from lqua

for all entries in itab1

where matnr = itab1-matnr

and wdatu in s_wdatu.

loop at itab1 into wa1.

move-corresponding wa1 to wa.

loop at itab2 into wa2 where matnr = wa1-matnr.

wa-wdatu = wa1-wdatu.

append wa to itab.

endloop.

sort itab.

delete adjacent duplicates comparing all fields.

loop at itab into wa.

write wa.

endloop.

Re: selection from 3 tables

Former Member — Fri, 11 Nov 2005 05:36:59 GMT

Hi,

Here is my program...

I have five select-options...

s_matnr,s_werks,s_lgort,s_wdatu,s_prdha.

So end user can select any of this options.Iam trying to write select statements based on this.

REPORT YMINVAGE no standard page heading

line-size 145

line-count 65

message-id Z1.

tables : mchb, "Batch Stocks

mara, "Material Master

mseg,

lqua, "Storage bin location

mvke.

data: begin of t_mchb occurs 0,

matnr like zpartcnv-zzpartfj,

matnr like mara-matnr,

werks like mchb-werks,

lgort like mchb-lgort,

ersda like mchb-ersda, " creation date

charg like mchb-charg, " Batch Number

clabs like mchb-clabs, " unrestricted quantity

cspem like mchb-cspem, " Blocked stock

prdha like mara-prdha, " product hierarchy

wdatu like lqua-wdatu, " gr date

qty type i,

price(8) type p decimals 2,

amount(8) type p decimals 2,

end of t_mchb.

data: begin of t_qty occurs 0,

matnr like mchb-matnr,

werks like mchb-werks,

lgort like mchb-lgort,

wdatu like lqua-wdatu,

clabs like mchb-clabs,

cspem like mchb-cspem,

charg like mchb-charg,

price(8) type p decimals 2,

qty30 like mchb-clabs,

qty60 like mchb-clabs,

qty90 like mchb-clabs,

qtyful like mchb-clabs,

amt30 like mseg-dmbtr,

amt60 like mseg-dmbtr,

amt90 like mseg-dmbtr,

amtful like mseg-dmbtr,

end of t_qty.

data: begin of t_coll occurs 0,

matnr like mchb-matnr,

werks like mchb-werks,

lgort like mchb-lgort,

clabs like mchb-clabs,

cspem like mchb-cspem,

charg like mchb-charg,

amount(8) type p decimals 2,

date30 type d,

date60 type d,

date90 type d,

fdate type d,

end of t_coll.

data: begin of t_mara occurs 0,

matnr like mara-matnr,

prdha like mara-prdha,

end of t_mara.

data : zukurs type p decimals 3,

zffact like tcurr-ffact,

ztfact like tcurr-tfact.

selection-screen begin of block sel with frame title text-001.

select-options : s_matnr for mchb-matnr.

select-options : s_prdha for mara-prdha.

select-options : s_werks for mchb-werks.

select-options : s_lgort for mchb-lgort.

select-options : s_wdatu for lqua-wdatu.

selection-screen end of block sel.

selection-screen begin of block sel1 with frame title text-002.

parameters: p_actstk radiobutton group g1 default 'X',

p_blkstk radiobutton group g1.

selection-screen end of block sel1.

call function 'Z_FILL_MATNR_SELTAB'

TABLES

s_matnr = s_matnr.

start-of-selection.

perform get_data.

perform process_data.

perform display_data.

top-of-page.

perform set_page_header.

&----

*& Form process_data

&----

text

----

form get_data.

select matnr prodh from mvke into table t_mara where

matnr in s_matnr and

prodh in s_prdha.

select * into corresponding fields of table t_mchb
from mchb
inner join mara
on mchb_{matnr = mara}matnr
inner join lqua
on mchb_{matnr = lqua}matnr
and mchb_{werks = lqua}werks
where mchb~matnr in s_matnr
and mara~prdha in s_prdha
and lqua~wdatu in s_wdatu.

loop at t_mara.

CALL FUNCTION 'CONVERSION_EXIT_MATN1_OUTPUT'

EXPORTING

INPUT = t_mara-matnr

IMPORTING

OUTPUT = t_mara-matnr.

modify t_mara.

endloop.

CHECK NOT t_mara[] IS INITIAL.

if p_actstk = 'X'.

select matnr werks lgort ersda charg clabs

into corresponding fields of table t_mchb from mchb

FOR ALL ENTRIES IN t_mara

where matnr = t_mara-matnr and

werks in s_werks and

lgort in s_lgort and

clabs > 0.

else.

select matnr werks lgort ersda charg clabs

into corresponding fields of table t_mchb from mchb

FOR ALL ENTRIES IN t_mara

where matnr = t_mara-matnr and

werks in s_werks and

lgort in s_lgort and

cspem > 0.

endif.

loop at t_mchb.

for date of goods receipt

select single * from lqua where matnr = t_mchb-matnr and

werks = t_mchb-werks and

charg = t_mchb-charg and

wdatu in s_wdatu.

t_mchb-wdatu = lqua-wdatu.

if sy-subrc = 0.

modify t_mchb.

clear t_mchb.

endif.

endloop.

loop at t_mchb.

for unit price

select single * from mseg where matnr = t_mchb-matnr and

charg = t_mchb-charg and

werks = t_mchb-werks.

clear zukurs.

perform get_usd.

if zukurs <> 0.

t_mchb-price = ( mseg-dmbtr / mseg-menge ) / ( zukurs / 100 ).

else.

t_mchb-price = ( mseg-dmbtr / mseg-menge ) / '1.6'.

endif.

if sy-subrc = 0.

modify t_mchb.

clear t_mchb.

endif.

endloop.

if sy-subrc ne 0.

message i016.

leave list-processing.

endif.

loop at t_mchb.

t_coll-matnr = t_mchb-matnr.

t_coll-werks = t_mchb-werks.

t_coll-lgort = t_mchb-lgort.

append t_coll.

clear t_coll.

endloop.

sort t_coll.

delete adjacent duplicates from t_coll.

endform. "get_data

&----

*& Form process_data

&----

text

----

form process_data.

data:age type d.

data: g_qty30 type i,

g_qty60 type i,

g_qty90 type i,

g_qtyful type i,

g_amt30 like t_qty-amt30,

g_amt60 like t_qty-amt30,

g_amt90 like t_qty-amt30,

g_amtful like t_qty-amt30.

loop at t_coll.

loop at t_mchb where matnr = t_coll-matnr

and werks = t_coll-werks

and lgort = t_coll-lgort.

age = t_mchb-wdatu.

if age lt 31.

if p_actstk = 'X'.

g_qty30 = g_qty30 + t_mchb-clabs.

else.

g_qty30 = g_qty30 + t_mchb-cspem.

endif.

elseif age gt 30 and age lt 61.

if p_actstk = 'X'.

g_qty60 = g_qty60 + t_mchb-clabs.

else.

g_qty60 = g_qty60 + t_mchb-cspem.

endif.

elseif age gt 60 and age lt 91.

if p_actstk = 'X'.

g_qty90 = g_qty90 + t_mchb-clabs.

else.

g_qty90 = g_qty90 + t_mchb-cspem.

endif.

else.

if p_actstk = 'X'.

g_qtyful = g_qtyful + t_mchb-clabs.

else.

g_qtyful = g_qtyful + t_mchb-cspem.

endif.

endloop.

for amount calculation

if t_mchb-price = '0.00'.
t_mchb-price = '1'.
endif.

g_amt30 = g_qty30 * t_mchb-price.

g_amt60 = g_qty60 * t_mchb-price.

g_amt90 = g_qty90 * t_mchb-price.

g_amtful = g_qtyful * t_mchb-price.

t_qty-amt30 = g_amt30.

t_qty-amt60 = g_amt60.

t_qty-amt90 = g_amt90.

t_qty-amtful = g_amtful.

t_qty-matnr = t_mchb-matnr.

t_qty-werks = t_mchb-werks.

t_qty-lgort = t_mchb-lgort.

t_qty-wdatu = t_mchb-wdatu.

t_qty-clabs = t_mchb-clabs.

t_qty-cspem = t_mchb-cspem.

t_qty-price = t_mchb-price.

*for quantities

t_qty-qty30 = g_qty30.

t_qty-qty60 = g_qty60.

t_qty-qty90 = g_qty90.

t_qty-qtyful = g_qtyful.

if g_qty30 ne 0 or g_qty60 ne 0

or g_qty90 ne 0 or g_qtyful ne 0.

append t_qty.

endif.

clear t_qty.

clear: g_qty30, g_qty60, g_qty90, g_qtyful,

g_amt30, g_amt60, g_amt90, g_amtful.

endloop.

sort t_qty by wdatu.

endform. "process_data

&----

*& Form display_data

&----

text

----

form display_data.

data: count type i.

data: l_ind value '1'.

loop at t_qty.

count = count + 1.

if l_ind = '1'.

l_ind = '0'.

format color col_total intensified off .

else.

l_ind = 1.

format color col_normal.

endif.

write:/1(24) t_qty-matnr color 2,

24(4) t_qty-werks,

32(4) t_qty-lgort.

WRITE:41(1) SY-VLINE NO-GAP.

WRITE:68(1) SY-VLINE NO-GAP.

WRITE:95(1) SY-VLINE NO-GAP.

WRITE:119(1) SY-VLINE NO-GAP.

if age < 31.

write: 42(13) t_qty-qty30,

55(13) t_qty-amt30.

elseif age < 61.

write: 69(11) t_qty-qty60,

84(11) t_qty-amt60.

elseif age < 91.

write: 96(11) t_qty-qty90,

108(11) t_qty-amt90.

else.

write: 120(11) t_qty-qtyful,

131(12) t_qty-amtful.

endif.

endloop.

uline.

write count.

endform. "display_data

&----

*& Form GET_USD

&----

text

----

form get_usd.

call function 'READ_EXCHANGE_RATE'

exporting

date = lqua-wdatu

foreign_currency = 'USD'

local_currency = 'SGD'

type_of_rate = 'M'

importing

exchange_rate = zukurs

foreign_factor = zffact

local_factor = ztfact

valid_from_date =

exceptions

no_rate_found = 1

no_factors_found = 2

no_spread_found = 3

others = 4.

endform. " GET_USD

&----

*& Form set_page_header

&----

text

----

FORM set_page_header.

call function 'Z_REPORT_TITLE'
EXPORTING
line_size = sy-linsz
sy_title = 'Inventory Aging Report'
uline = 'X'
first_page_only = ' '.

write:/1(145) space color 1.

write :1(20) 'Material' color col_heading,

23(9) 'Plant' color col_heading,

31(15) 'Str.loc' color col_heading,

51(18) '0 - 30 days' color col_heading,

76(18) ' 31 - 60 days ' color col_heading,

100(15) ' 61 - 90 days ' color col_heading,

125(14) ' > 91 days ' color col_heading.

write:/1(145) space color 1.

write:48(11) 'Qty' color col_heading,

58(10) 'Amnt(USD)' color col_heading.

write:73(10) 'Qty' color col_heading,

82(10) 'Amnt(USD)' color col_heading.

write:98(10) 'Qty' color col_heading,

108(10) 'Amnt(USD)' color col_heading.

write:122(10) 'Qty' color col_heading,

132(10) 'Amnt(USD)' color col_heading.

uline.

endform. "set_page_header

Re: selection from 3 tables

Former Member — Fri, 11 Nov 2005 05:41:33 GMT

Dont use Selct inside the loop

loop at t_mchb.
* for date of goods receipt
select single * from lqua where matnr = t_mchb-matnr and
werks = t_mchb-werks and
charg = t_mchb-charg and
wdatu in s_wdatu.

In the previous post i have mentioned how to retrieve the data right?

Refer that.