topic Re: Inner join issue in Application Development and Automation Discussions

Inner join issue

Former Member — Fri, 06 Mar 2009 06:07:09 GMT

i was microsft sql 2000 user.

where is use join like this

select max(Date) from Table1 , table2

where table1.field = abc

and table2.field = table1.field.

but in abap it says santax error like comma with select???

Re: Inner join issue

Former Member — Fri, 06 Mar 2009 06:12:37 GMT

use this syntax:

select table1_{fieldA table2}fieldB

from table1 inner join table 2

on table1-field1 = table2-field1

where filed2 = ....

Re: Inner join issue

awin_prabhu — Fri, 06 Mar 2009 06:14:08 GMT

Hi,

Abap query differs in syntax.

Ex:

SELECT max( Table1~Date )

INTO CORRESPONDING FIELDS OF TABLE itab

FROM Table1 INNER JOIN Table2

ON Table1_{field = Table2}field

WHERE Table1~field = 'abc'.

See F1 help on inner join.

Thanks..

Re: Inner join issue

Former Member — Fri, 06 Mar 2009 06:14:11 GMT

Hi,

In abap iner joins are keep in follwoing way:

select * from table1 inner join table2 on table1_{field1 = table2}field2.

For max value

SELECT max( Table1~Field2 )

INTO CORRESPONDING FIELDS OF TABLlE tmp

FROM Table1 INNER JOIN Table2

ON Table1_{field1 = Table2}field1

WHERE Table1~field1 = 'abc'.

tmp is internal table

To knwo more about jojns example look at the link below:

http://www.sap-img.com/abap/inner-joins.htm

Thanks,

Smita

Edited by: Smita Gupta on Mar 6, 2009 7:15 AM

Re: Inner join issue

I355602 — Fri, 06 Mar 2009 06:16:01 GMT

Hi,

Use like:-


data : v_date type table2-date.

select
max( table2~date)
into v_date "date variable
from table1
inner join table2
on table1~field1 = table2~field1
where
table1~field = 'ABC'.

Hope this helps you.

Regards,

Tarun

Re: Inner join issue

Former Member — Fri, 06 Mar 2009 06:17:40 GMT

Hi,

Insted of inner join go for For all entries. It is reduces DB trafic and better performance.

Regards

Md.MahaboobKhan

Re: Inner join issue

Former Member — Fri, 06 Mar 2009 06:18:30 GMT

write as :

select max( Date ) into v_date

from Table1

inner join table2

on table1_{field = table2}field

where table1~field = 'abc'

and table1_{field = table2}field.

Re: Inner join issue

Former Member — Fri, 06 Mar 2009 06:20:58 GMT

Hi Ahmed,

Just write 'Inner join' and press F1 in the abap edition (SE38), you will get the syntax for inner join.

With luck,

Pritam.

Re: Inner join issue

Former Member — Fri, 06 Mar 2009 06:21:34 GMT

here i am giving inner join sample code for if we join 3 tables please check and correct for you question

Select single Vbrk_{Bukrs Vbrk}Kunrg Vbrk~Vbeln

Vbrk_{Fkdat Vbrk}Bstnk_Vf Vbrk~Zterm

Tvzbt~Vtext

Vbak_{Vbeln Vbak}Bstdk

Likp_{Vbeln Likp}lfdat Likp~Lfuhr

into w_vbrk

from vbrk

inner join Tvzbt on Tvzbt_{Zterm = Vbrk}Zterm and

Tvzbt~Spras = sy-langu

Inner join Vbfa as SalesLnk

on SalesLnk~vbeln = pu_vbeln and

SalesLnk~vbtyp_v = c_order

inner join Vbak on Vbak_{Vbeln = SalesLnk}Vbelv

Inner join Vbfa as DeliveryLnk

on DeliveryLnk~vbeln = pu_vbeln and

DeliveryLnk~vbtyp_v = c_Delivery

inner join Likp on Likp_{Vbeln = DeliveryLnk}Vbelv

where vbrk~vbeln = pu_Vbeln.

Thanks and regards

Durga.K

Re: Inner join issue

Former Member — Fri, 06 Mar 2009 06:22:45 GMT

Hi Harris,

Syntax for Inner Join as well as simple Select query is different in ABAP.

Syntax :

Select .<Field List with tabaliase>...

from [(] {dbtab_left [AS tabalias_left]} | join

{[INNER] JOIN}|{LEFT [OUTER] JOIN}

{dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

e.g:

 SELECT c~carrname p~connid f~fldate 
       INTO CORRESPONDING FIELDS OF TABLE itab 
       FROM ( ( scarr AS c 
         INNER JOIN spfli AS p ON p~carrid   = c~carrid 
                                         AND p~cityfrom = p_cityfr 
                                        AND p~cityto   = p_cityto ) 
         INNER JOIN sflight AS f ON f~carrid = p~carrid 
                                AND f~connid = p~connid ).

There is lot's of information already available in this forum which you can search.

U can also use transaction ABAPDOCU to learn these statements.

reg

Ashwani

Re: Inner join issue

Former Member — Fri, 06 Mar 2009 06:53:11 GMT

Hi ,

Abap syntex is diffrent bcz it is open sql.

so use f1 help for syntex and abapdocu for sample programs.

use like this.......

SELECT max( Tab1~Date )

INTO CORRESPONDING FIELDS OF TABLE it_tab

FROM Tab1 INNER JOIN Tab2

ON Tab1_{field = Tab2}field

WHERE Tab1~field = 'xyz'.