How to fetch data from 2 tables

[Former Member]

Hi Experts,

I am using 2 Ztables.Let us assume that there are 2 fields in Table1 as a and b and 2nd Table contain c as field.I want to retrive data from these tables by combining the fields of Table1 with that of 2.

Eg: I want to compare a+b with c and fetch records.

Is there any logic to combine 2 fields and compare with 3rd field using a single select statement?

Regds,

Sam.

[Sandra_Rossi]

that may probably be done in native SQL but not with Open SQL. SAP recommends to always use Open SQL.

[Former Member]

Hi Sandra,

Thanx for ur reply.I am fetching data from these tables using se38.Can I link these 2 tables using internal tables?

Regds,

Sam

[Sandra_Rossi]

Yes, abap statement SELECT ... FOR ALL ENTRIES IN itab WHERE ... Please check it in abap documentation, there is an example in the doc. You may also check abap examples in SE38, Environment, Examples, ABAP examples.

[Former Member]

If the field in second table is of type string, then you can first concatenate values from of a and b from first table into string variable and thn in select statement on second table you can compare that variable with the second table field.

[Clemenss]

Hi Sam,

if the tables have one or more common key fields, you can use a JOIN condition for the tables, i.e.

data:
begin of ls_target,
  a like ztable1-a,
  b like ztable1-b,
  c like ztable2-c,
end of ls_target,
lt_target like table of  ls_target.

select ztable1~a ztable1~b ztable2~c
  into corresponding fields of table lt_target
  from ztable1
  join ztable2 ON  ztable2~key = ztable1~key 
  where <your condition>.

Note ahead:

1) some people say select into corresponding fields is not good for performance. I think it is good to avoid errors and nobody ever gave a perfomance proof.

2) Most developers always use an alias such as ztable1AS a, ztable2 AS b. This is due to the fact that after table joins were introduced with release 3.1, the ABAP sample programs an documentation always used the ALIAS clause although it is not necessary nor does it establish transparency.

Kind regards

Clemens

[Sandra_Rossi]

Just react about your 2 points to say I'm happy to understand things like you :

1) I made a test on a 7.0 release, and this was exactly the same performance on a very big table (around 1 million record if I remember well) with many fields : I ran the tests several times, and I'm pretty sure that the abap compiler transforms the "into corresponding fields of" into a static list of fields.

2) I didn't know, that explains why people still do like that (as often it's something inherited from past. I also prefer fully naming the tables (i.e. not using the alias).