topic Re: itab with 2 diffrnet DB tables fields in Application Development and Automation Discussions

itab with 2 diffrnet DB tables fields

Former Member — Thu, 07 Aug 2008 12:14:57 GMT

hi experts!!,

i have a scenario where i need to select fields A,B,C,and D . A,B,C are in 1 DB table ex DB1 and D field is in other DB table ex DB2. how to select these 4 fields to a internal table.

plz tell me how to define a itab for this and how to fetch the values for the same.

plz help.

mahesh

Re: itab with 2 diffrnet DB tables fields

Former Member — Thu, 07 Aug 2008 12:19:59 GMT

REPORT ZZZZ000000.

tables:mara,marc,mard,makt.

data:begin of it_mara occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

meins like mara-meins,

end of it_mara.

data:begin of it_marc occurs 0,

matnr like marc-matnr,

pstat like marc-pstat,

werks like marc-werks,

end of it_marc.

data:begin of it_mard occurs 0,

werks like mard-werks,

lgort like mard-lgort,

labst like mard-labst,

end of it_mard.

data:begin of it_final occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

meins like mara-meins,

pstat like marc-pstat,

werks like marc-werks,

lgort like mard-lgort,

labst like mard-labst,

maktx like makt-maktx,

end of it_final.

select-options:s_matnr for mara-matnr.

select matnr

mtart

meins

from mara

into table it_mara

where matnr in s_matnr.

if not it_mara[] is initial.

select matnr

pstat

werks

from marc

into table it_marc

for all entries in it_mara

where matnr = it_mara-matnr.

if not it_marc[] is initial.

select werks

lgort

labst

from mard

into table it_mard

for all entries in it_marc

where werks = it_marc-werks.

endif.

loop at it_mara.

it_final-matnr = it_mara-matnr.

it_final-mtart = it_mara-mtart.

it_final-meins = it_mara-meins.

read table it_marc with key matnr = it_mara-matnr.

it_final-werks = it_marc-werks.

it_final-pstat = it_marc-pstat.

read table it_mard with key werks = it_marc-werks.

it_final-lgort = it_mard-lgort.

it_final-labst = it_mard-labst.

if sy-subrc = 0.

select maktx from makt into it_final-maktx where matnr = it_final-matnr.

endselect.

endif.

append it_final.

endloop.

loop at it_final.

write:/ it_final-matnr under 'material',

it_final-mtart under 'material type',

it_final-meins under 'unit of measure',

it_final-werks under 'plant' ,

it_final-pstat under 'status',

it_final-lgort under 'storage loc',

it_final-labst under 'stock',

it_final-maktx.

endloop.

Re: itab with 2 diffrnet DB tables fields

Former Member — Thu, 07 Aug 2008 12:21:35 GMT

hi.

first of all check common fields in both the table if find then use for all entries to club the data from both the table.

else check out the table that have common fields from db1 and db2. like it has field a or b or c and d. for common table try to check value table of all four fields.

and try to join all the three for for all entries.

for the syntex for 'for all entries ' check out F1 or SDN.

Re: itab with 2 diffrnet DB tables fields

Former Member — Thu, 07 Aug 2008 12:21:46 GMT

Hi,

To retrieve the data from 2 different tables a common field should be there, in your case do you have a common field? Then write a select query with for all entries.

Regards,

Satish Reddy.

Re: itab with 2 diffrnet DB tables fields

Former Member — Thu, 07 Aug 2008 12:23:00 GMT

Hi,

First you SELECT A B C Fields from DB Table put into one ITAB and then SELECT D field from DB1 Table ITAB2 and move to these Fields to ITAB3. and Modify the internal table.

Regards,

Sridhar.

Re: itab with 2 diffrnet DB tables fields

SuhaSaha — Thu, 07 Aug 2008 12:23:22 GMT

Hello,

If these two tables are related i mean they have key fields in common then you can JOIN these two tables DB1 & DB2 into the internal table ITAB.

BR,

Suhas

Re: itab with 2 diffrnet DB tables fields

Former Member — Thu, 07 Aug 2008 12:23:45 GMT

create a internal table with those four field, fetch the data into the internal table from the 2 DB table using join...

With luck,

Pritam.

Re: itab with 2 diffrnet DB tables fields

Former Member — Thu, 07 Aug 2008 12:23:50 GMT

Hi,

Use join or for all entries

for example on join check this link:

for example on for all entries check this link:

Regards

Adil

Re: itab with 2 diffrnet DB tables fields

former_member404244 — Thu, 07 Aug 2008 12:24:13 GMT

Hi,

is there any common field(key) available between DB1 and DB2...if yes then u can use inner join and put it in one internal table...

if u don't have any key field then do like this

select a b c from db1 into correspoding fields of table itab1...

select d from db2 appending corresponding fields of table itab1.

now u will get the a b c and d data in one internal table..

Regards,

Nagaraj

Re: itab with 2 diffrnet DB tables fields

narin_nandivada3 — Thu, 07 Aug 2008 12:25:05 GMT

Hi,

Please check this code....


* Field string declarations for 3rd internal table
Data:
   Begin of fs_db3,
	a type db1-a,
	b type db1-b,
	c type db1-c,
	d type db2-d,
   End of fs_db3,

* Third internal table
     t_db3 like 
  standard table 
	of fs_db3,
*Workarea of 3rd internal table
  wa_db3 like line of t_db3.
  
data:
   wa_db1 like line of t_db1, " Work area for first internal table
   wa_db2 like line of t_db2. " Work area for second internal table

  select * from db1 into t_db1.
  select * from db2 into t_db2.

  append lines of t_db1 to t_db3.

  loop at t_db2 into wa_db2.
    
    wa_db3-d = wa_db2-d.
    modify t_db3 from wa_db3 index sy-tabix.	
   
  endloop.

Hope this would help you and solve your issue.

Good luck

Narin

Re: itab with 2 diffrnet DB tables fields

Former Member — Thu, 07 Aug 2008 12:25:45 GMT

Hi,

You can Retreive the Data from two database table into one internal table through Join Statement.

For that there must be common field in both the table.

Another method is that you can use two internal table for two database table. An one final table to store final Records.You can use For all entries in this case.For this also common fields should be there.

If you want to simply populate the internal table without checking field similarity then go for Append Lines of itab.

Modify the final internal table tranporting that field.

Regards,

Sujit

Re: itab with 2 diffrnet DB tables fields

Former Member — Thu, 07 Aug 2008 12:27:50 GMT

Hii!

Check out this sample code


REPORT z_sdn.

TABLES:
  sflight.

SELECT-OPTIONS:
  s_carrid FOR sflight-carrid,
  s_connid FOR sflight-connid.
DATA:
  BEGIN OF fs_tab,
   carrid TYPE sflight-carrid,
   connid TYPE sflight-connid,
   fldate TYPE sflight-fldate,
   bookid TYPE sbook-bookid,
  END OF fs_tab.

DATA:
  t_tab LIKE
  TABLE OF
        fs_tab.


START-OF-SELECTION.

SELECT f~carrid
       f~connid
       f~fldate
       b~bookid
  FROM ( sflight AS f
       INNER JOIN sbook AS b ON f~carrid = b~carrid
                            AND f~connid = b~connid
                            AND f~fldate = b~fldate )
  INTO TABLE t_tab
 WHERE f~carrid IN s_carrid
   AND f~connid IN s_connid.

LOOP AT t_tab INTO fs_tab.
  WRITE: / fs_tab-carrid,
           fs_tab-connid,
           fs_tab-fldate,
           fs_tab-bookid.
ENDLOOP.

Regards

Abhijeet

Re: itab with 2 diffrnet DB tables fields

narin_nandivada3 — Thu, 07 Aug 2008 12:33:33 GMT

Hi,

You said there is no D field in the first internal table and its only present in 2nd table.... so how can you think that join would be possible... as join or for all entries needs common fields..

Good luck

Narin..