selecting data from data base tables into itab with multiple select queries

[MNaveen]

Hi all,

i am dealing with a situation where in there is 3 DB tables in all say

1. ITAB - ZTBL_MAIN.

2. DB table - ZTBL_SUB1.

3. DB table - ZTBL_SUB2.

ZTBL_MAIN contains feilds from both tables 2 and 3 so to choose some records from ZTBL_SUB1 into the ITAB a select query has been written as below.

SELECT * FROM ZTBL_SUB1 INTO CORRESPONDING FIELDS OF TABLE ZTBL_MAIN

WHERE <condition>.

now i also want some feilds in main for each record to be filled from the other DB table for which if i use another select query after the above one in my report prog. say..

SELECT * FROM ZTBL_SUB2 INTO CORRESPONDING FIELDS OF TABLE ZTBL_MAIN

WHERE <condition>.

then all the previous records are going to be erased..Kindly let me know a way in which i can implement the above implementation.

Thanks&Rgds,

Naveen M

[JozsefSzikszai]

hi,

the second select has to be like:

SELECT ... APPENDING CORRESPONDING FIELDS OF ...

hope this helps

ec

[former_member585060]

Check the common fields from Table 2 and Table 3, use Inner Join to fetch all the data from the 2 tables.

Syntax

SELECT a_{field1 a}field2 b_{field1 b}field2 INTO CORRESPONDING FIELDS OF TABLE itab

FROM ZTBL_SUB1 AS a

INNER JOIN ZTBL_SUB2 AS b ON a_{field3 = b}field3

AND a_{field4 = b}field4

AND a_{field5 = b}field5.

Edited by: Bala Krishna on Sep 5, 2008 4:56 PM

[Former Member]

Hi,

The second select statement would be

SELECT * FROM ZTBL_SUB2 APPENDING CORRESPONDING FIELDS OF TABLE ZTBL_MAIN

WHERE <condition>.

Regards,

Surinder

[Former Member]

Use append in the select query

SELECT * FROM ZTBL_SUB1 Appending CORRESPONDING FIELDS OF TABLE ZTBL_MAIN

WHERE <condition>.

SELECT * FROM ZTBL_SUB2 Appending CORRESPONDING FIELDS OF TABLE ZTBL_MAIN

WHERE <condition>.

[MNaveen]

Hi,

When i tried to implement ur suggestion. A syntax error came up saying the table Z_main does not have an header line.

i am making use of one of the fields of the itab to define in where condition.

How do we acces the fields of the itab which is declared without headerline.

SELECT * FROM z_tbl_sub2 APPENDING CORRESPONDING FIELDS OF TABLE z_tbl_main

WHERE <field1> = z_tbl_main-<field1>.

Thnks & Rgds,

Naveen M

[Former Member]

Hi,

After the first select statement copy all the records in the internal table z_tbl_main

into another internal table say temp_main

and then use try with this select statement

SELECT * FROM z_tbl_sub2 APPENDING CORRESPONDING FIELDS OF TABLE z_tbl_main

for all entries in temp_main

WHERE <field1> = temp_main-<field1>.

[MNaveen]

hi,

Ur suggestions solved the syntax error. However my intial problem was not solved i.e., using the statement

SELECT * FROM ztbl_sub2 APPENDING CORRESPONDING FIELDS OF TABLE ztbl_main FOR ALL ENTRIES IN ztbl_main

WHERE <field1> = ztbl_main-<field1>.

creates or appends some more entries into the itab thats all.. But that is not what i am trying to implement.

As explained before the first select query would have filled certain fields for all records in ztbl_main from ztbl_sub1.

Now in the second select query i want to fill other fields of ztbl_main for all the records already fetched using first select query by using my where condition.

The above select query that was suggested only creates new records in the table ztab_main but does not fill the fields of the records as i expected.

how do i fullfil my requirment..

kindly help.

thnks & Rgds,

Naveen M

[former_member585060]

Hi,

As sujested in previous post of mine, use INNER JOIN for

ZTBL_SUB1, ZTBL_SUB2 and fill ur ZMAIN internal Table.

SELECT a_{field1 a}field2 b_{field1 b}field2 INTO CORRESPONDING FIELDS OF TABLE itab

FROM ZTBL_SUB1 AS a

INNER JOIN ZTBL_SUB2 AS b ON a_{field3 = b}field3

AND a_{field4 = b}field4

AND a_{field5 = b}field5.

[MNaveen]

Hello all,

Thanks for ur time.

Thnks & Rgds,

Naveen M

[Former Member]

Hi,

If all the tables are related then u can use join condition to achieve this.

Regards,

Karthik

[Former Member]

Hi Naveen,

Please try with this method.

1) Select * from ztab1into itab1.

2) Select * from ztab2 into itab2 for all entries in itab1 where <field> eq itab1-<field>.

3) Loop At itab2 into wa_itab2.

Read Table itab1into wa_itab1with key <field> = wa_itab2-<field>.

Check for sy-subrc eq 0.

Now move all the fields from itab1 to itab2.

for e.g wa_itab2-<field1> = wa_itab1-<field1>.

Then,

Modify itab2 from wa_itab2.

Endloop.