Skip to Content
author's profile photo Former Member
Former Member

Join Tables

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 KNA1KUNNR EQ VBAKKUNNR

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

8 Answers

  • Best Answer
    Posted on Jul 04, 2007 at 06:10 AM

    Hi Ram,

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

    <b>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 kna1kunnr = vbakkunnr

    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.</b>

    <b><i>Reward points... if you feel useful.</i>

    Cheers !

    Moqeeth.</b>

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 04, 2007 at 06:15 AM

    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.

    <b>JOINING TABLES IN THIS BLOCK</b>

    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 d1vbeln = d2vbeln

    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 .

    <b>Reward points if helful</b>

    regards

    Aarti

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 04, 2007 at 06:20 AM

    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 AKUNNR ALAND1 BVBELN BAUDAT BAUART BNETWR

    INTO TABLE IT_OUTPUT

    FROM KNA1 AS A

    INNER JOIN VBAK AS B

    ON AKUNNR EQ BKUNNR

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 04, 2007 at 06:30 AM

    give full stop in the loop statement

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 04, 2007 at 05:57 AM

    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 AKUNNR ALAND1 BVBELN BAUDAT BAUART BNETWR

    INTO TABLE IT_OUTPUT

    FROM KNA1 AS A

    INNER JOIN VBAK AS B

    ON AKUNNR EQ BKUNNR

    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.

    <b>Reward points if that was helpful.</b>

    Kiran

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 04, 2007 at 05:58 AM

    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 KNA1KUNNR KNA1LAND1 VBAKVBELN VBAKAUDAT VBAKAUART VBAKNETWR

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 04, 2007 at 06:25 AM

    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 KNA1KUNNR KNA1LAND1 VBAKVBELN VBAKAUDAT VBAKAUART VBAKNETWR

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 04, 2007 at 06:29 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.