topic Re: help please in Application Development and Automation Discussions

help please

Former Member — Sun, 21 Jan 2007 09:43:07 GMT

I've 2 tables .... table #1 have 4 fields out of which 2 fields only present in the table #2 ...... can I output the data of all the fields that are in both the tables.

thanks in advace.

Re: help please

Former Member — Sun, 21 Jan 2007 10:08:11 GMT

Hi Shaheen,

Are the two fields in the table 1 and 2 are Key fields?? What are the key fields present in both the tables ? If they are key fields we can retrive the data from the two tables...

Srini

Re: help please

Former Member — Sun, 21 Jan 2007 10:17:09 GMT

thanks for your reply ..... no they are not the key fields.

Re: help please

Former Member — Sun, 21 Jan 2007 10:18:45 GMT

Try using Joining the tables by Inner Join to retrieve the data

Re: help please

Former Member — Sun, 21 Jan 2007 10:18:49 GMT

how can we retrieve the data if they were the key fields

Re: help please

athavanraja — Sun, 21 Jan 2007 10:26:08 GMT

if there are no common values to compare between table 1 and table 2 how do you want to combine records? may be first record in tab1 with first rec in tab 2?

Re: help please

Former Member — Sun, 21 Jan 2007 10:26:10 GMT

check this example... combination of fields from two tables....

SELECT P_{CARRID P}CONNID F~FLDATE

INTO TABLE ITAB

FROM SPFLI AS P

INNER JOIN SFLIGHT AS F ON P_{CARRID = F}CARRID AND

P_{CONNID = F}CONNID

<b>WHERE P~CITYFROM = 'FRANKFURT' AND

P~CITYTO = 'NEW YORK'</b>.

in the join give the link between two tables.

regds,

kiran

Re: help please

Former Member — Sun, 21 Jan 2007 10:39:38 GMT

no what i ment is that there are common fields between the 2 but not all of the are the key fields.

Re: help please

Former Member — Sun, 21 Jan 2007 10:42:17 GMT

you give the fields which are common in the two tables in the condition inner join,

as my above example.

in that example carrid and connid are common in both the tables sflight and spfli.

regds,

kiran

Re: help please

Former Member — Sun, 21 Jan 2007 10:52:14 GMT

ok .... what should i do next

Re: help please

Former Member — Sun, 21 Jan 2007 10:59:10 GMT

declare all the required fields into one internal table.

assume first table as spfli and second table as sflight

write the code in this format.

SELECT P_{CARRID P}CONNID F~FLDATE

INTO TABLE ITAB

FROM SPFLI AS P

INNER JOIN SFLIGHT AS F ON P_{CARRID = F}CARRID AND

P_{CONNID = F}CONNID

WHERE P~CITYFROM = 'FRANKFURT' AND

P~CITYTO = 'NEW YORK'.

1. the p_{carrid p}connid f~fldate are the fields which are required from two tables

2. inner join sflight as f on p_{carrid on = f}carrid and p_{connid = f}connid.

is the condition between two tables.

if you are not yet clear , give me the fields and table names which you want to query.

regds,

kiran

Re: help please

athavanraja — Sun, 21 Jan 2007 11:10:06 GMT

the inner join Kiran is referring is for database tables.

for itabs

let us assume you have

tab1 with 4 fields

tab 2 with 2 fields

field 1 in tab1 and field 1 in tab2 are the connecting values.

and you wan to move them to new itab

loop at itab1.

read table itab2 with key field1 = itab1-field1

if sy-subrc eq 0 .

move: fileds from itab1 and itab2 to new itab.

append new itab.

endif .

endloop .

REgards

Raja

Re: help please

Former Member — Sun, 21 Jan 2007 11:20:11 GMT

my tables are mlgt and mlgn.

Re: help please

Former Member — Sun, 21 Jan 2007 11:26:00 GMT

these are database tables.

i do not have access to SAP system right now. if possible can u give the fields of both tables, so that i can make a query.

otherwise do like this....

data itab1 like table of mlgt with header line.

data itab2 like table of mlgn with header line.

(delcare itab3 with all the fields you required).

select * from mlgt into table itab1.

select * from mlgn into table itab2

loop at itab1.

read table itab2 with <col> = itab1-<col>

if sy-subrc eq 0.

move all itab1 and itab2 to itab3(final table).

append itab3.

endif.

endloop.

loop at itab3.

write : / itab3-............

endloop.

in the above example change <col> with the existing columns in two tables.

regds,

kiran

Re: help please

Former Member — Sun, 21 Jan 2007 11:43:12 GMT

Hi Shaheen,

You can use inner join like this: This is only example you need to use according to your main table & details table.

SELECT MLGN_{MATNR MLGN}LGNUM

INTO TABLE ITAB

FROM MLGN

INNER JOIN MLGT ON MLGN_{MATNR = MLGT}MATNR AND

MLGN_{LGNUM = MLGT}LGNUM

WHERE MLGN~LGNUM= '1000'.

For more information on INNER JOINS I like to tell to you that in real time scenario generally dont use INNER JOINS because of Database performance. Instead of this SELECT FOR ALL ENTRIES is preferable.

Ashven

Re: help please

Former Member — Mon, 22 Jan 2007 04:09:13 GMT

not yet solved ..... help

Re: help please

seshatalpasai_madala — Mon, 22 Jan 2007 04:16:37 GMT

Hi,

Try this query,

DATA: it_tab type table of mlgn.

SELECT MLGN_{MATNR MLGN}LGNUM

FROM MLGN

INNER JOIN MLGT ON MLGN_{MATNR = MLGT}MATNR AND

MLGN_{LGNUM = MLGT}LGNUM INTO CORRESPONDING FIELDS OF IT_TAB

WHERE MLGN~LGNUM= '1000'.

Regards,

Sesh

Re: help please

Former Member — Mon, 22 Jan 2007 04:26:36 GMT

still not getting the required

Re: help please

Former Member — Mon, 22 Jan 2007 04:44:51 GMT

can I have the syntax for SELECT FOR ALL ENTRIES

Re: help please

Former Member — Mon, 22 Jan 2007 05:23:18 GMT

Data: begin of it_mlgn occurs 0,

matnr like mlgn-matnr,

lgnum like mlgn-lgnum,

lgbkz like mlgn-lgbkz,

end of it_mlgn.

data: begin of it_mlgt occurs 0,

matnr like mlgt-matnr,

lgnum like mlgt-lgnum,

lgpla like mlgt-lgpla,

end of it_lgpla.

select matnr lgnum gbkz from mlgn into table it_mlgn

where ...

if not it_mlgn[] is initial.

sort it_mlgn by matnr lgnum.

select matnr lgnum lgpla from mlgt from mlgt

into table it_mlgt

for all entries in it_mlgn

where matnr = it_mlgn-matnr

lgnum = it_mlgn-lgnum.

endif.