topic Re: Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Tue, 07 Nov 2006 10:03:38 GMT

Hello I need to do something like this

v_a = 'table1'

v_b = 'table2'

Select a.field1, b.field2

from (v_a) as a inner join (v_b) as b

on a.field1 = b.field2.

This sentence is giving me an error in the inner join, how can this be done?

Thanks

Re: Inner Join

Former Member — Tue, 07 Nov 2006 10:04:52 GMT

Select a~field1, b~field2 into table itab
          from v_a as a inner join v_b as b
           on a~field1 = b~field2.

Re: Inner Join

Former Member — Tue, 07 Nov 2006 10:06:44 GMT

do not use a.field1, b.field2. u should not use . or ,

use a_{field1 b}field2

Select a_{field1 b}field2

from (v_a) as a inner join (v_b) as b

on a_{field1 = b}field2.

Have a look at below link for sample code:

http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_InnerJoinStatement.asp

Best Regards,

Vibha

*Please mark all the helpful answers

Re: Inner Join

anversha_s — Tue, 07 Nov 2006 10:07:35 GMT

Hi,

chk this.

Select a~field1, b~field2 into table int_table
          from v_a as a 
              inner join v_b as b
               on a~field1 = b~field2.

Another sample code.

table emp 
empno name
a sasi
b xxx 
c yyy

table sal
empno salary
a 1000
b 2000


Inner join
****************
select e~empno e~name 
s~sal 
into table int_table
from emp as e
inner join sal
on 
e~empno = s~empno.

if you made inner join between table a and b by emp no 
the selection retrives only if the condition satisfy the output will be

a sasi 1000
b xxx 2000

Outer join
*************************
select e~empno e~name 
s~sal into table  int_table
from emp as e
LEFT OUTER JOIN sal
on 
e~empno = s~empno.

if you made outer join (left /right ) the left table kept as it is the 
if the condition satisfy the right table entries will fetch else leave it blank
the output will be

a sasi a 1000
b xxx b 2000
c yyy

if hlped mark points.

Rgds

Anver

Re: Inner Join

Former Member — Tue, 07 Nov 2006 10:08:16 GMT

and also dont use comma(,)

Re: Inner Join

Former Member — Tue, 07 Nov 2006 10:09:35 GMT

sorry I put dots instead of - (too much java background ) but the instructions I'm looking for needs to read the table names from a table, then pass it to the SQL.

Re: Inner Join

Former Member — Tue, 07 Nov 2006 10:12:26 GMT

Hi,

select v_a_{field1 v_b}field2

into table itab

from v_a inner join v_b

on v_a_{field1 = v_b}field2.

syntax is as above but it looks meaningless since you are trying to display the same field twice as u r equating the 2 fields

check it once

Re: Inner Join

Former Member — Tue, 07 Nov 2006 10:14:15 GMT

I really doubt if this works as the fields can not be determined until runtime.

Anyhow have to check myself personally on system.

Kind Regards

Eswar

Re: Inner Join

Former Member — Tue, 07 Nov 2006 10:17:35 GMT

I will try to explain myself better.

The name of the tables are in one table. I need to read the table and capture the table names, then pass it to the sql. For example I'm able to do this

v_a = 'table1'.

SELECT a-1

FROM (v_a) as a

WHERE ....

But apparantely I cannot do this with two tables, something like this

SELECT ...

FROM (va_a) as a inner join (va_b) as b on ...

Re: Inner Join

anversha_s — Tue, 07 Nov 2006 10:22:43 GMT

Hi Scott ,

if u like to use dynamic way of assaigning fields, chk this.

The below is a wonderfull piece of coding were we can dynamically access the table using inner join and without mentioning the field names !!!

PARAMETERS: P_MATNR TYPE MATNR.
 
DATA: BEGIN OF ITAB OCCURS 0.
INCLUDE STRUCTURE MARA.
DATA: WERKS LIKE MARC-WERKS,
END OF ITAB.
 
DATA: BEGIN OF DY_FIELDS OCCURS 0,
FIELD(61),
END OF DY_FIELDS.
 

DATA: FIELDS LIKE DFIES OCCURS 0 WITH HEADER LINE.
 
CALL FUNCTION 'DDIF_NAMETAB_GET'
EXPORTING
tabname = 'MARA'
TABLES
DFIES_TAB = FIELDS
EXCEPTIONS
NOT_FOUND = 1
OTHERS = 2
.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
 
 
 
LOOP AT FIELDS.
 
CLEAR: DY_FIELDS.
 
CONCATENATE 'A~' FIELDS-FIELDNAME INTO DY_FIELDS-FIELD.
 
APPEND DY_FIELDS.
 
ENDLOOP.
 

DY_FIELDS = 'B~WERKS'.
APPEND DY_FIELDS.
 
SELECT (DY_FIELDS)
INTO TABLE ITAB
FROM MARA AS A INNER JOIN MARC AS B
ON A~MATNR = B~MATNR
WHERE A~MATNR = P_MATNR.
 

CHECK SY-SUBRC = 0.

Rgds

Anver

if hlped pls mark points

Re: Inner Join

Former Member — Tue, 07 Nov 2006 10:28:34 GMT

Sorry Anversha but I don't see where you use an inner join with two dinnamic tables. I only see how to use the dinamyc fields which is not exactly what I'm looking for.

Thanks

Re: Inner Join

Former Member — Tue, 07 Nov 2006 10:33:23 GMT

Dear Scott

Have just tried on my system to unvail. <b>Not able to perform the join</b> statement when any of the table is referenced in a dynamic way.

Will try to explore more on this when i get back to office tomorrow.

Kind Regards

Eswar