ALV Report: Get all columns from one table and few columns from other table

[Former Member]

Hello,

I have developed an ALV Report in which,

1) I select data using select * from one table into internal table which is defined as TYPE STANDARD TABLE OF database table.

2) Build the field catalog using 'LVC_FIELDCATALOG_MERGE' and using the DB table as a structure.

3) Display grid using CALL METHOD g_grid->set_table_for_first_display.

Now there is some change in the requirement such that, I need to add few fields from other table into the layout. This new table is related to the existing table.

So please advice me,

1) How can I use inner join? Whether can I use select * from one table and select few fields from other table in inner join ?

Thanks and regards,

Anand

[Former Member]

hi,

eg:

SELECT

a~lifnr

a~ktokk

a~name1

a~name2

a~name3

a~name4

a~ernam

a~erdat

b~bukrs

b~akont

b~zwels

b~uzawe

b~zterm

b~xpore

FROM lfa1 AS a INNER JOIN lfb1 AS b ON a_{lifnr = b}lifnr

INTO CORRESPONDING FIELDS OF TABLE i_input_temp

WHERE a~lifnr IN s_lifnr

AND a~brsch IN s_brsch

AND a~erdat IN s_erdat

AND a~ernam IN s_ernam

AND a~konzs IN s_konzs

AND a~ktokk IN s_ktokk

AND b~bukrs IN s_bukrs.

Hope this sample code helps.

Regards,

Subramanian

[Former Member]

Forgot to mention that first table has 180 fields. So if I join two tables, I have to manually write A~ FILED1, A_{FIELD2......A}FIELD180 for joining wiht another table...

Which I do not want to do?

Pls advice how can I write :

Select A_{* , B}field1 B~field2 from table1 as A inner join table2 as B .....

Thanks and rgds,

Anand

[Former Member]

Hi Anand,

If you can not write those many fields of your table in select statement, then declare two internal tables, one each for two tables.

while doing a select on second table use "For all entries in Itab1" variant and select only those records which are in itab1.

declare a internal table with combination of fields you wanted. Loop on one itab, read the records from other itab and append them into this target internal table.

-- sample code for your understanding--

1. Select * from dbtab1

into table Itab1

where -

---

2. select * from dbtab2

into table itab2

for all entries in itab1

where field1 = itab1-field1and

field2 = itab1-field2.

now declare itab3 as you wish.

loop on itab1 into wa1.

move all fields of wa1 into wa3 fields.

read itab2 into wa2 with key fields.

move all fields of wa2 into wa3 fields.

append wa3 to itab3.

clear wa3.

endloop.

Thats it.

regards,

Simha

[venkatasriram_mygapula]

Hi,

i think we can not use select* for inner joins means fetching the data from two or more tablse. other wise you can try like this. first select all fields from one table into one internal table. and select all fields from second table into second internal table by using for all entries. finally momdify the second internal table fields into first internal table fields. have a look.

select * from <dbatble> into table itab

where <conditon>.

if sy-subrc = 0.

sort itab by <keyfield>.

endif.

if not itab[] is initial.

select * from <dbtable> into table jtab

for all entries in itab where <conditon with previous table(itab)>

endif.

loop at itab.

read table jtab with key <conditon>.

assign all fields from jtab to itab.

modify itab transporting all fields.

endloop.

finally you can display itab which is having all fields from itab and jtab.

regards.

sriram

[uwe_schieferstein]

Hello Anand

If you frequently need data from these two DB tables you may think of creating a DB view joining these two tables in the DDIC.

However, these DB views sometimes give funny selection results. Yet if the selected records are as expected you may use this DB view in the SELECT statements in your reports.

Regards

Uwe