topic Re: GETTING DATA FROM TWO TABLES. in Application Development and Automation Discussions

GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:14:21 GMT

Hi,

I am having i_matgrp table with fields matkl , l_sum , l_t_sum.

I need to get material group description(wgbez,wgbez60) from t023t table for each matkl in i_matgrp

and put all 3 fields and descriptions(2 fields) in to a single table i_display for display.

please suggest select query for this...

Thanks in advance.

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:16:00 GMT

Hi Anil,

Try using inner joins.

Here is a sample query,

SELECT p~cityfrom " Departure city

p~cityto " Arrival city

p~carrid " Airline Code

p~deptime " DEPTIME

p~arrtime " Arrival time

p~connid " Flight Connection Number

f~fldate " Flight date

f~price " Airfare

INTO TABLE t_flight

FROM spfli AS p INNER JOIN sflight AS f

ON ( p_{carrid = f}carrid )

AND ( p_{connid = f}connid )

WHERE ( p~cityfrom IN s_ctyfro )

and ( p~cityto IN s_cityto )

AND ( p~carrid IN s_carrid ).

where s_cityto and s_carrid arethe select option.

Much Regards,

Amuktha.

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:17:45 GMT

you can use inner join. You can use this code fragment as a reference.


SELECT
      A~matnr
      A~werks
      A~lgort
      A~labst
      B~matkl
      C~maktx
   INTO TABLE it_val
   FROM mard as A INNER JOIN mara as B ON B~matnr = A~matnr
   INNER JOIN makt as C ON A~matnr = C~matnr
   WHERE
      A~werks in c_werks AND
      A~lgort in c_lgort AND
      A~matnr in pro_code AND
      B~matkl in mat_grp  AND
      A~labst <> 0.

Regards,

Mon Magallanes

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:19:06 GMT

Hi,

Try it out using FOR ALL ENTERIES statement.

first select the fields in i_mat table and then use the above statement to fetch(wgbez,wgbez60).

Regards

Rajesh Kumar

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:19:17 GMT

Hi,

select matkl 
                  wgbez 
                  wgbez60 
           from t023t into table i_display 
for all entries in i_matgrp.

loop at i_display.
read table i_display from i_display field value matkl = i_matgrp-matkl.
modify table i_display from i_display transporting wgbez wgbez60.
endloop.

this will work

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:20:08 GMT

Hi,

use inner joins.

Refer this query.

data :

begin of fs,

carrid type spfli-carrid,

connid type spfli-connid,

fldate type sflight-fldate,

price type sflight-price,

end of fs.

data t_sflight like standard table of fs.

select a~carrid

a~connid

b~fldate

b~price

from ( spfli as a inner join sflight as b on

a_{carrid eq b}carrid and a_{connid eq b}connid )

into table t_sflight.

Re: GETTING DATA FROM TWO TABLES.

former_member222860 — Tue, 17 Feb 2009 07:23:34 GMT

Its sample inner join

data: begin of itab occurs 0,
        matnr like mara-matnr,
        maktx like makt-maktx,
      end of itab.

select * into corresponding fields of table itab
from ( mara as a
  inner join makt as b on a~matnr = b~matnr ).

loop at itab.
 write:/ itab-matnr, itab-maktx.
endloop.

Pl. search the forum,

thanks\

Mahesh

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:23:35 GMT

Hi,

If the two tables are the same, then you can try using the except operation as follows:

select * from tablea

except

select * from tableb

If they share a common primary key, then you could also do a left join between the table and filter on the absence of data in the second table to find the rows missing:

select tablea.*

from tablea

left join tableb on tablea.rowid = tableb.rowid

where tableb.rowid is null

Regards,

Neelima.

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:25:28 GMT

data: begin of itab,
matkl like i_matgrp-matkl,
wgbez like t023t-wgbez,
wgbez60 like t023t-wgbez60 ,
end of itab.

select i_matgrp~matkl t023t~wgbez t023t~wgbez60 
into corresponding fields of table itab
from i_matgrp
inner join t023t on ( i_matgrp~matkl = t023t~matkl).

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:30:24 GMT

Hi tahir,

it is giving error as

i_matgrp is not defined as table or view in the ABAP data dictionay,

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:43:51 GMT

data: begin of itab,
matkl like t023t,
wgbez like t023t-wgbez,
wgbez60 like t023t-wgbez60 ,
end of itab.
 
select matkl wgbez wgbez60 
into corresponding fields of table itab
from t023t.

i was thinking i_matgrp is dictionary table.

u can select all material group and their description in this way.

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:48:27 GMT

Hi,

you can implement this using below two methods:

1.inner join as explained above

2.Use FOR ALL ENTERIES like

a.decsribe 3 intrenal table i_matgrp,i_t023t,and i_display.

b.Select all the fields from in the i_matgrp

c.then select all the enetried in the i_t023t for all enteries in i_matgrp

d.finally read all the data from the two internal table into i_display

select matkl l_sum l_t_sum from matgrp into table i_matgrp where matk = matk.
"supposing the value of matk is provided by user or if all the data then remove the where condition
if sy-subrc = 0.
select matkl wgbez wgbez60 from t023t into table i_t230t for all enteries in i_matgrp where matk = i_matk.
endif.

loop at i_matkgrp into wa_matkgrp.
wa-matkl = wa_matkgrp-matkl.
wa-l_sum = wa_matkgrp-l_sum.
wa- l_t_sum = wa_matkgrp-l_t_sum.
loop at i_t230t into wa-t230.
wa-wgbez = wa_t230-wgbez.
wa-wgbez60 = wa_t230-wgbez60.

append wa into itab.
clear wa.
endloop.
enloop.

Hope this might help you out.

Pooja

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 07:57:25 GMT

If ur first table i_matgrp having data.

Then find the logic below for ur query.

if i_matgrp is not initial.

select SPRAS
       MATKL
       WGBEZ
       WGBEZ60
       into table i_display
for all entries in i_matgrp
where matkl eq i_matgrp-matkl.


endif.

Pls let me know if any queries regarding this.

Thanks & Regards,

Syed.

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 08:01:47 GMT

I am having i_matgrp table with fields matkl , l_sum , l_t_sum.

I need to get material group description(wgbez,wgbez60) from t023t table for each matkl in i_matgrp

and put all 3 fields and descriptions(2 fields) in to a single table i_display for display.

please suggest select query for this...

hi.

sorry do like in this way.just copy paste it and check t

but also paste ur code for the internal table of i_matgrp which u have made

data: begin of itab,
matkl like t023t-matkl,
wgbez like t023t-wgbez,
wgbez60 like t023t-wgbez,
end of itab.
data:wa_itab like itab.
data:wa_i_matgrp like i_matgrp.

data: begin of i_display,
matkl like t023t-matkl,
l_sum type i,
l_t_sum type i, "these two fields can be according to ur need,
wgbez like t023-wgbez,
wgbez60 like t023t-wgbez60,
end of i_display.

data: wa_i_display like i_display.

select matkl wgbez wgbez60 into corresponding fields of itab
from t023t FOR ALL ENTRIES IN i_matgrp
where matkl = i_matgrp-matkl.

loop at i_matgrp into wa_i_matgrp.
  MOVE-CORRESPONDING wa_i_matgrp to wa_i_display.
  read table itab into wa_itab WITH KEY matkl = wa_i_matgrp-matkl.
   MOVE-CORRESPONDING wa_itab to wa_i_display.
  append wa_i_display to i_display.
endloop.

Edited by: tahir naqqash on Feb 17, 2009 1:07 PM

Re: GETTING DATA FROM TWO TABLES.

Former Member — Tue, 17 Feb 2009 08:19:12 GMT

Hi.

Sorry my Sap was down so i cant test my code.

now i have checked it and correct it.

now u have to fill ur i_matgrp table according to ur need by select or by append. however i have tested it for 001 materal group.

REPORT ztn_test.
data: BEGIN OF i_matgrp OCCURS 10,
      matkl like t023t-matkl,
      l_sum type i,
      i_t_sum type i,
  END OF i_matgrp.
" To test for matkl = 001.
  " but u should fill ur i_matgrp according to ur need by select statement or append, which u are using
  i_matgrp-matkl = '001'. append i_matgrp.


data: begin of itab OCCURS 10,
matkl like t023t-matkl,
wgbez like t023t-wgbez,
wgbez60 like t023t-wgbez,
end of itab.
data:wa_itab like itab.
data:wa_i_matgrp like i_matgrp.

data: begin of i_display occurs 10,
matkl like t023t-matkl,
l_sum type i,
l_t_sum type i, "these two fields can be according to ur need,
wgbez like t023t-wgbez,
wgbez60 like t023t-wgbez60,
end of i_display.

data: wa_i_display like i_display.

select matkl wgbez wgbez60 into corresponding fields of table itab
from t023t FOR ALL ENTRIES IN i_matgrp
where matkl = i_matgrp-matkl.

loop at i_matgrp into wa_i_matgrp.
  MOVE-CORRESPONDING wa_i_matgrp to wa_i_display.
  read table itab into wa_itab WITH KEY matkl = wa_i_matgrp-matkl.
   MOVE-CORRESPONDING wa_itab to wa_i_display.
  append wa_i_display to i_display.
endloop.