topic Fetching data from 3 tables in Application Development and Automation Discussions

Fetching data from 3 tables

Former Member — Fri, 18 Jan 2008 11:58:29 GMT

Hi All,

I trying to fetch values from 3 tables and pass the values into an internal table.

Code

SELECT KNA1_{KTOKD KNA1}KUNNR KNB1_{BUKRS KNVV}VKORG KNVV_{VTWEG KNVV}SPART KNVV~LOEVM

INTO

(ITAB-KTOKD,ITAB-KUNNR,ITAB-BUKRS, ITAB-VKORG,ITAB-VTWEG,ITAB-SPART, ITAB-LOEVM)

FROM ( ( KNA1

INNER JOIN KNB1

ON KNA1_{KUNNR = KNB1}KUNNR)

KNA1

INNER JOIN KNVV

ON KNA1_{KUNNR = KNVV}KUNNR ) .

I am getting an error message.

"The Format of the Field Specificatio "KNB1~KUNNR" is not supported.

What is the problem here?

Regards

Aby

Re: Fetching data from 3 tables

former_member188829 — Fri, 18 Jan 2008 12:17:33 GMT

Hi,

check this program..

DATA:BEGIN OF ITAB OCCURS 0,

KTOD LIKE KNA1-KTOKD,

KUNNR LIKE KNA1-KUNNR,

BUKRS LIKE KNB1-BUKRS,

VKORG LIKE KNVV-VKORG,

VTWEG LIKE KNVV-VTWEG,

SPART LIKE KNVV-SPART,

LOEVM LIKE KNVV-LOEVM,

END OF ITAB.

SELECT KNA1_{KTOKD KNA1}KUNNR KNB1_{BUKRS KNVV}VKORG KNVV_{VTWEG KNVV}SPART

KNVV~LOEVM

INTO TABLE ITAB

FROM KNA1

INNER JOIN KNB1

ON KNA1_{KUNNR = KNB1}KUNNR

INNER JOIN KNVV

ON KNA1_{KUNNR = KNVV}KUNNR .

LOOP AT ITAB.

WRITE:/ ITAB-KTOD,ITAB-KUNNR,ITAB-BUKRS,ITAB-VKORG,ITAB-VTWEG,ITAB-SPART

,ITAB-LOEVM.

ENDLOOP.

Re: Fetching data from 3 tables

Former Member — Fri, 18 Jan 2008 12:29:55 GMT

Hi Aby,

Take a look at th ebold line. . You nee dto switch 'KNA1_{KUNNR' and ' KNB1}KUNNR' .

I also used aliases just to be sure (optional here i think)

SELECT k_{ktokd k}kunnr b_{bukrs v}vkorg v_{vtweg v}spart v~loevm

INTO

(itab-ktokd,itab-kunnr, itab-bukrs, itab-vkorg,itab-vtweg,itab-spart, itab-loevm)

FROM ( ( kna1 AS k

INNER JOIN knb1 AS b ON b_{kunnr = k}kunnr )

INNER JOIN knvv AS v ON v_{kunnr = k}kunnr ) .

ENDSELECT.

Please also refer to this example from help.sap.com:

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

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 ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

Regards, and reward if usefull please.

Joris

Re: Fetching data from 3 tables

Former Member — Fri, 18 Jan 2008 12:31:17 GMT

good

instead of using the table name try to use the reference names for the respective table names and than try, apart from this check with your join statement again.

thanks

mrutyun^

Re: Fetching data from 3 tables

Former Member — Fri, 18 Jan 2008 12:38:59 GMT

Hi,

It should be:

FROM ( ( KNA1

INNER JOIN KNB1

ON KNA1_{KUNNR = KNB1}KUNNR

INNER JOIN KNVV

ON KNA1_{KUNNR = KNVV}KUNNR ) .

Regards,

John.