topic Re: Join Tables in Application Development and Automation Discussions

Join Tables

Former Member — Wed, 04 Jul 2007 05:50:10 GMT

Hi,

I want to join two tables KNA1 and VBAK With common field as KUNNR. I want the following fields in the join table kunnr, land1, vbelnaudat,auart,netwr.

This is the program i wrote. plz let me know the errors.

TYPES:

BEGIN OF TY_OUTPUT,

KUNNR TYPE KNA1.KUNNR,

LAND1 TYPE KNA1.LAND1,

VBELN TYPE VBAK.VBELN,

AUDAT TYPE VBAK.AUDAT,

AUART TYPE VBAK.AUART,

NETWR TYPE VBAK.NETWR,

END OF TY_OUTPUT.

DATA:

ST_OUTPUT TYPE TY_OUTPUT,

IT_OUTPUT TYPE TABLE OF TY_OUTPUT.

SELECT OPTIONS.

SKUNNR FOR ST_OUTPUT.KUNNR.

SELECT KNA1_{KUNNR EQ VBAK}KUNNR

WHERE KNA1~KUNNR IN SKUNNR.

IF SY-SUBRC EQ 0

LOOP AT IT_OUTPUT INTO ST_OUTPUT

WRITE:/ ST_OUTPUT.KUNNR, ST_OUTPUT.LAND1,

ST_OUTPUT_NAME1, ST_OUTPUT.VBELN,ST_OUTPUT.AUDAT,AT_OUTPUT.AUART,ST_OUTPUT.NETWR.

ENDLOOP.

Re: Join Tables

Former Member — Wed, 04 Jul 2007 05:57:19 GMT

Hi Ram,

This is the corrected code,

TYPES:

BEGIN OF TY_OUTPUT,

KUNNR TYPE KUNNR,

LAND1 TYPE LAND1,

VBELN TYPE VBELN,

AUDAT TYPE AUDAT,

AUART TYPE AUART,

NETWR TYPE NETWR,

END OF TY_OUTPUT.

DATA:

ST_OUTPUT TYPE TY_OUTPUT,

IT_OUTPUT TYPE TABLE OF TY_OUTPUT.

SELECT-OPTIONS:

SKUNNR FOR ST_OUTPUT-KUNNR.

SELECT A_{KUNNR A}LAND1 B_{VBELN B}AUDAT B_{AUART B}NETWR

INTO TABLE IT_OUTPUT

FROM KNA1 AS A

INNER JOIN VBAK AS B

ON A_{KUNNR EQ B}KUNNR

WHERE A~KUNNR IN SKUNNR.

IF SY-SUBRC EQ 0.

LOOP AT IT_OUTPUT INTO ST_OUTPUT.

WRITE:/ ST_OUTPUT-KUNNR, ST_OUTPUT-LAND1,

ST_OUTPUT-NAME1, ST_OUTPUT-VBELN, ST_OUTPUT-AUDAT, AT_OUTPUT-AUART, ST_OUTPUT-NETWR.

ENDLOOP.

Reward points if that was helpful.

Kiran

Re: Join Tables

Former Member — Wed, 04 Jul 2007 05:58:57 GMT

TYPES:

BEGIN OF TY_OUTPUT,

KUNNR TYPE KNA1-KUNNR,

LAND1 TYPE KNA1-LAND1,

VBELN TYPE VBAK-VBELN,

AUDAT TYPE VBAK-AUDAT,

AUART TYPE VBAK-AUART,

NETWR TYPE VBAK-NETWR,

END OF TY_OUTPUT.

DATA:

ST_OUTPUT TYPE TY_OUTPUT,

IT_OUTPUT TYPE TABLE OF TY_OUTPUT.

SELECT OPTIONS.

SKUNNR FOR ST_OUTPUT-KUNNR.

SELECT KNA1_{KUNNR KNA1}LAND1 VBAK_{VBELN VBAK}AUDAT VBAK_{AUART VBAK}NETWR

FROM KNA1 AS KNA1

JOIN VBAK AS VBAK

WHERE KNA1~KUNNR IN SKUNNR.

IF SY-SUBRC EQ 0

LOOP AT IT_OUTPUT INTO ST_OUTPUT

WRITE:/ ST_OUTPUT-KUNNR, ST_OUTPUT-LAND1,

ST_OUTPUT_NAME1, ST_OUTPUT-VBELN,ST_OUTPUT-AUDAT,ST_OUTPUT-AUART,ST_OUTPUT-NETWR.

ENDLOOP.

Use the join condition.

Remove the dots in your query and replace it with '-'.

Select whatever records you want.

Check the above code.

Regards,

Pavan P.

Re: Join Tables

mohammed_moqeeth — Wed, 04 Jul 2007 06:10:23 GMT

Hi Ram,

I have done necessary correction in your code, here is the code for you:

TABLES: kna1.

TYPES:

BEGIN OF ty_output ,

kunnr TYPE kna1-kunnr,

name1 TYPE kna1-name1,

land1 TYPE kna1-land1,

vbeln TYPE vbak-vbeln,

audat TYPE vbak-audat,

auart TYPE vbak-auart,

netwr TYPE vbak-netwr,

END OF ty_output.

DATA:

st_output TYPE ty_output,

it_output TYPE TABLE OF ty_output.

SELECT-OPTIONS skunnr FOR kna1-kunnr.

SELECT kna1~kunnr

kna1~name1

kna1~land1

vbak~vbeln

vbak~audat

vbak~auart

vbak~netwr

FROM kna1 INNER JOIN vbak ON kna1_{kunnr = vbak}kunnr

INTO CORRESPONDING FIELDS OF TABLE it_output

WHERE kna1~kunnr IN skunnr.

IF sy-subrc EQ 0.

LOOP AT it_output INTO st_output.

WRITE:/ st_output-kunnr,

st_output-name1,

st_output-land1,

st_output-vbeln,

st_output-audat,

st_output-auart,

st_output-netwr.

ENDLOOP.

SKIP.

WRITE: / 'SY-SUBRC VALUE:', sy-subrc.

ENDIF.

Reward points... if you feel useful.

Cheers !

Moqeeth.

Re: Join Tables

Former Member — Wed, 04 Jul 2007 06:15:49 GMT

hi rams

i have done similiar coding for my prog, may be it helps for your code also:

TYPES: BEGIN OF ty_vbak,

vbeln TYPE vbak-vbeln, "ORDER NO

erdat TYPE vbak-erdat, "DATE OF CREATION

kunnr TYPE vbak-kunnr, " SOLD-TO-PARTY

vkorg TYPE vbak-vkorg, "SALES ORGANIZATION

vtweg TYPE vbak-vtweg, "DIST CHANNEL

spart TYPE vbak-spart, "DIVISION

posnr TYPE vbap-posnr, "ITEM NUMBER

matnr TYPE vbap-matnr, "MATERIAL NO

arktx TYPE vbap-arktx, "MATERIAL DESCRIPTION

kwmeng TYPE vbap-kwmeng, "QUANTITY

kunnr_sh TYPE vbpa-kunnr, "SHIP-TO-PARTY

END OF ty_vbak,

BEGIN OF ty_vbpa,

vbeln1 TYPE vbpa-vbeln, "ORDER NO.

posnr1 TYPE vbpa-posnr, "ITEM NO

kunnr TYPE vbpa-kunnr, "SHIP-TO-PARTY

parvw TYPE vbpa-parvw, "PARTNER FUNCTION

END OF ty_vbpa.

DATA: it_vbak TYPE STANDARD TABLE OF ty_vbak,

it_vbpa TYPE STANDARD TABLE OF ty_vbpa,

wa_vbak TYPE ty_vbak,

wa_vbpa TYPE ty_vbpa.

SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE text-t01.

SELECT-OPTIONS: s_vbeln FOR vbak-vbeln ,

s_vkorg FOR vbak-vkorg ,

s_vtweg FOR vbak-vtweg,

s_spart FOR vbak-spart,

s_matnr FOR mara-matnr,

s_erdat FOR vbak-erdat .

PARAMETERS: s_kunnr TYPE vbak-kunnr.

SELECTION-SCREEN: END OF BLOCK b1.

JOINING TABLES IN THIS BLOCK

SELECT d1~vbeln

d1~erdat

d1~kunnr

d1~vkorg

d1~vtweg

d1~spart

d2~posnr

d2~matnr

d2~arktx

d2~kwmeng

FROM vbak AS d1 INNER JOIN vbap AS d2 ON d1_{vbeln = d2}vbeln

INTO CORRESPONDING FIELDS OF TABLE it_vbak

WHERE d1~vbeln IN s_vbeln AND

d1~erdat IN s_erdat AND

d1~vkorg IN s_vkorg AND

d1~vtweg IN s_vtweg AND

d1~spart IN s_spart AND

d1~kunnr = s_kunnr AND

d2~matnr IN s_matnr .

Reward points if helful

regards

Aarti

Re: Join Tables

Former Member — Wed, 04 Jul 2007 06:20:00 GMT

TYPES:

BEGIN OF TY_OUTPUT,

KUNNR TYPE KUNNR,

LAND1 TYPE LAND1,

VBELN TYPE VBELN,

AUDAT TYPE AUDAT,

AUART TYPE AUART,

NETWR TYPE NETWR,

END OF TY_OUTPUT.

DATA:

ST_OUTPUT TYPE TY_OUTPUT,

IT_OUTPUT TYPE TABLE OF TY_OUTPUT.

SELECT-OPTIONS:

SKUNNR FOR ST_OUTPUT-KUNNR.

SELECT A_{KUNNR A}LAND1 B_{VBELN B}AUDAT B_{AUART B}NETWR

INTO TABLE IT_OUTPUT

FROM KNA1 AS A

INNER JOIN VBAK AS B

ON A_{KUNNR EQ B}KUNNR

WHERE A~KUNNR IN SKUNNR.

IF SY-SUBRC EQ 0.

LOOP AT IT_OUTPUT INTO ST_OUTPUT.

WRITE:/10 ST_OUTPUT-KUNNR,

20 ST_OUTPUT-LAND1,

30 ST_OUTPUT-NAME1,

40 ST_OUTPUT-VBELN,

50 ST_OUTPUT-AUDAT,

60 ST_OUTPUT-AUART,

70 ST_OUTPUT-NETWR.

ENDLOOP.

Reward points if that was helpful.

WITH REGARDS,

SURESH.A

Re: Join Tables

Former Member — Wed, 04 Jul 2007 06:25:11 GMT

Hi,

I had modified and wrote the program,

Still it is showing the error.. Statement concluding with "...ST_OUTPUT-KUNNR" ended unexpectedly.

TYPES:

BEGIN OF TY_OUTPUT,

KUNNR TYPE KNA1-KUNNR,

LAND1 TYPE KNA1-LAND1,

VBELN TYPE VBAK-VBELN,

AUDAT TYPE VBAK-AUDAT,

AUART TYPE VBAK-AUART,

NETWR TYPE VBAK-NETWR,

END OF TY_OUTPUT.

DATA:

ST_OUTPUT TYPE TY_OUTPUT,

IT_OUTPUT TYPE TABLE OF TY_OUTPUT.

SELECT OPTIONS:

SKUNNR FOR ST_OUTPUT-KUNNR.

SELECT KNA1_{KUNNR KNA1}LAND1 VBAK_{VBELN VBAK}AUDAT VBAK_{AUART VBAK}NETWR

FROM KNA1 AS KNA1

JOIN VBAK AS VBAK

WHERE KNA1~KUNNR IN SKUNNR.

IF SY-SUBRC EQ 0

LOOP AT IT_OUTPUT INTO ST_OUTPUT

WRITE:/ ST_OUTPUT.KUNNR, ST_OUTPUT.LAND1,

ST_OUTPUT_NAME1, ST_OUTPUT.VBELN,ST_OUTPUT.AUDAT,AT_OUTPUT.AUART,ST_OUTPUT.NETWR.

ENDLOOP.

Re: Join Tables

Former Member — Wed, 04 Jul 2007 06:29:24 GMT

Give Start-of-selection before the select statement and give full stop in sy-subrc check and loop statement.

Message was edited by:

Sumi Vasu

Re: Join Tables

Former Member — Wed, 04 Jul 2007 06:30:39 GMT

give full stop in the loop statement

Re: Join Tables

Former Member — Wed, 04 Jul 2007 06:35:39 GMT

error is at 24th row and the error is as follows.

Statement concluding with "...ST_OUTPUT-KUNNR" ended unexpectedly.