topic Re: error in INNER JOIN in Application Development and Automation Discussions

error in INNER JOIN

Former Member — Fri, 08 Dec 2006 21:53:54 GMT

Hello friends,

I'm getting an error for a select statement involving an inner join. Could you check and clarify. I would appreciate it.

select *

from kna1 into a inner join knvv as b on a_{kunnr = b}kunnr

inner join knb1 as c on a_{kunnr = c}kunnr

inner join knvp as e on a_{kunnr = e}kunn2

where a~kunnr in s_kunnr.

Thanks!

Re: error in INNER JOIN

Former Member — Fri, 08 Dec 2006 22:00:07 GMT

There is problem in the query you have framed,

select *

from kna1 into <b>< Details missing here></b> a inner join knvv as b on a_{kunnr = b}kunnr

inner join knb1 as c on a_{kunnr = c}kunnr

inner join knvp as e on a_{kunnr = e}kunn2

where a~kunnr in s_kunnr.

A sample I found here in SDN,

SELECT a~mandt

a~vbeln

a~posnr

a~lprio

a~matnr

a~kwmeng

a~vrkme

a~netwr

a~waerk

a~kdmat

a~werks

a~pstyv

a~charg

a~route

u~absta

u~fksta

u~gbsta

u~lfsta

a~posex

a~vgbel

u~besta

v~bsark

v~ihrez

INTO TABLE i_vbap

FROM vbap AS a

INNER JOIN vbup AS u ON u_{vbeln = a}vbeln AND u_{posnr = a}posnr

INNER JOIN vbkd AS v ON v_{vbeln = a}vbeln AND v_{posnr = a}posnr

FOR ALL ENTRIES IN i_vbak

WHERE a~vbeln EQ i_vbak-vbeln.

Please refer how to use joins

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

Regards

Kathirvel

Re: error in INNER JOIN

Former Member — Fri, 08 Dec 2006 22:05:15 GMT

What kind of error - syntax, runtime?

Rob

Re: error in INNER JOIN

Former Member — Fri, 08 Dec 2006 22:06:50 GMT

Hi,

Check this change..

1) Remove the * and select the specific fields..

2) Have the INTO clause to move the values to the work area.

<b>select a~kunnr

into wa_kunnr

from kna1 as a</b> inner join knvv as b on a_{kunnr = b}kunnr

inner join knb1 as c on a_{kunnr = c}kunnr

inner join knvp as e on a_{kunnr = e}kunn2

where a~kunnr in s_kunnr.

Thanks,

Naren

Re: error in INNER JOIN

Former Member — Fri, 08 Dec 2006 22:10:43 GMT

You're missing the INTO clause. Try:


REPORT ztest MESSAGE-ID 00.

TABLES: kna1, knvv, knb1, knvp.

SELECT-OPTIONS: s_kunnr FOR kna1-kunnr.

DATA: BEGIN OF itab OCCURS 0,
        f1,
      END   OF itab.

SELECT *
  FROM kna1 AS a
    INNER JOIN knvv AS b
      ON a~kunnr = b~kunnr
    INNER JOIN knb1 AS c
      ON a~kunnr = c~kunnr
    INNER JOIN knvp AS e
      ON a~kunnr = e~kunn2
  INTO CORRESPONDING FIELDS OF TABLE itab
  WHERE a~kunnr IN s_kunnr.

ITAB is just a generic internal table for my example. You will have to put in the fields you want.

Rob

Message was edited by:

Rob Burbank

Re: error in INNER JOIN

Former Member — Fri, 08 Dec 2006 22:43:50 GMT

Thanks bro !

Re: error in INNER JOIN

Former Member — Fri, 08 Dec 2006 22:47:33 GMT

Glad to help - close the thread please.

Rob

Re: error in INNER JOIN

Clemenss — Fri, 08 Dec 2006 23:55:18 GMT

Hi,

although everything has been said:

There is no reason to use alias as from kna1 as a. Just say from kna1 and for instance whetre kna1~kunrr = ... This helps a lot for transparency. Hopefully one day SAP help will also get this.

There is a real need for an alias if you want to koin a table on itself as might be necessary i.e. with VBFA as VBFA_FOLLOWUP.

Also please note: If you use select * and you have the same field name with different contents in the joined tables, this field will not be filled at all. Avoid select * and specify the fields you need as tabname~fieldname. This is also best for performance.

Regards,

Clemens

Re: error in INNER JOIN

Former Member — Mon, 11 Dec 2006 21:46:16 GMT

thanks for all the help.