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

internal table with same variable and one select query

Hi,

I am a new bee here with may be a silly question.

I have a internal table as below.

DATA: BEGIN OF IT_ORDERDETAILS OCCURS 0,

VBELN LIKE VBAK-VBELN, "Order number

BSTNK LIKE VBAK-BSTNK, "customer PO

ERDAT LIKE VBAK-ERDAT, " Order created date

MATNR LIKE VBAP-MATNR, "Sales order line item

KWMENG LIKE VBAP-KWMENG, "Quantity

D_VBELN like likp-vbeln, " delivery no

POSNR like lips-posnr, " delivery item

KUNNR LIKE LIKP-KUNNR, "ship quantity

END OF IT_ORDERDETAILS.

Where VBELN field is in VBAK and LIKP table.

VBELN in VBAK table = order #

VBELN in LIKP table is = Delivery #

I want to use join to fetch data in single select query.

Below is the select query

SELECT VBAK~VBELN

VBAK~BSTNK

VBAK~ERDAT

VBAP~MATNR

VBAP~KWMENG

likp~vbeln

lips~posnr

LIPS~VGBEL

INTO (IT_ORDERDETAILSvbak, IT_ORDERDETAILSbstnk, IT_ORDERDETAILSerdat, IT_ORDERDETAILSmatnr, IT_ORDERDETAILSkwmeng, IT_ORDERDETAILSd_vbeln,IT_ORDERDETAILSposnr, IT_ORDERDETAILSkunnr)

FROM VBAK left outer JOIN VBAP ON ( VBAKVBELN = VBAPVBELN )

left outer JOIN LIPS ON ( VBAKVBELN = LIPSVGBEL )

join LIKP on ( LIPSVBELN = LIKPVBELN )

WHERE VBAK~ERDAT IN CR_DATE.

I am getting error in the query.

Please suggest.

Thanks,

Rajesh

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2010 at 02:45 PM

    Hi Rajesh,

    Try this.

    Either use the statement

    select single <rest of your query> -


    if u planning for only one set of data

    or use

    SELECT VBAK~VBELN

    VBAK~BSTNK

    VBAK~ERDAT

    VBAP~MATNR

    VBAP~KWMENG

    likp~vbeln

    lips~posnr

    LIPS~VGBEL

    INTO corresponding fields of table IT_ORDERDETAILS

    FROM VBAK left outer JOIN VBAP ON ( VBAKVBELN = VBAPVBELN )

    left outer JOIN LIPS ON ( VBAKVBELN = LIPSVGBEL )

    join LIKP on ( LIPSVBELN = LIKPVBELN )

    Hope this may be helpful.

    Regards,

    Sharin.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 14, 2010 at 02:58 PM

    The suggestion above will not work:

    SELECT VBAK~VBELN

    VBAK~BSTNK

    VBAK~ERDAT

    VBAP~MATNR

    VBAP~KWMENG

    likp~vbeln

    lips~posnr

    ---> LIPS~VGBEL

    INTO corresponding fields of table IT_ORDERDETAILS

    FROM VBAK left outer JOIN VBAP ON ( VBAKVBELN = VBAPVBELN )

    left outer JOIN LIPS ON ( VBAKVBELN = LIPSVGBEL )

    join LIKP on ( LIPSVBELN = LIKPVBELN )

    You did not difine a field named VGBEL.

    I would write the following:

    TYPES:
    BEGIN OF lty_s_order,
      VBELN TYPE VBAK-VBELN, "Order number
      BSTNK TYPE VBAK-BSTNK, "customer PO
      ERDAT TYPE VBAK-ERDAT, " Order created date
      MATNR TYPE VBAP-MATNR, "Sales order line item
      KWMENG TYPE VBAP-KWMENG, "Quantity
      D_VBELN TYPE likp-vbeln, " delivery no
      POSNR TYPE lips-posnr, " delivery item
      KUNNR TYPE LIKP-KUNNR, "ship-to party
    END OF lty_s_order,
    lty_t_order TYPE STANDARD TABLE OF lty_s_order.
    DATA: lt_order TYPE lty_t_order.
    
    SELECT a~vbeln a~bstnk a~erdat b~matnr b~kwmeng c~vbeln d~posnr c~kunnr
    INTO TABLE lt_order
    FROM vbak as a
    INNER JOIN vbap as b ON a~vbeln = b~vbeln
    INNER JOIN lips as d ON b~vbeln = d~vgbel
                                     AND b~posnr = d~vgpos
    INNER JOIN likp as c ON c~vbeln = d~vbeln
    WHERE a~erdat IN cr_date.
    

    However, this SELECT does not seem to be the high-performance solution. The parties are stored in VBPA, e.g.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2010 at 03:20 PM

    Rajesh,

    Read carefully the keyword documentation of JOIN keyword. You can get that from editor itself by highlighting the keyword and then pressing F1 key or through tcode 'ABAPDOCU' . I'm sure you will spot the error after going through this . That is the best way to learn on your own.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 14, 2010 at 08:59 PM

    Hi rajesh.nayakbola,

    although this is not quite the right place for this, let me give you some notes:

    1. Code should be

    formatted as code 

    by markin it with mouse and use above &lt;&gt; button.

    2. Internal tables shoult not be declared using OCCURS clause - this is last century style

    3. Internal tables do not need and should not have a header line, they should use TYPES for declaration

    4. Data should not be declared using LIKE: If they refer to dictionary TYPES, use TYPE. LIKE is only mandatory for data objects declared in your program, i.e. DATA IT_some_ORDERDETAILS like IT_ORDERDETAILS.

    5. If you get an error here, never write "I am getting error" but copy and paste the error message fully.

    - The fields in brackets in the INTO clause never have ~ character, there is no IT_ORDERDETAILS~vbak, only IT_ORDERDETAILS-vbeln

    It could be something like this:

    TYPES:
      BEGIN OF TY_ORDERDETAILS,
      VBELN TYPE VBAK-VBELN, "Order number
      BSTNK TYPE VBAK-BSTNK, "customer PO
      ERDAT TYPE VBAK-ERDAT, " Order created date
      MATNR TYPE VBAP-MATNR, "Sales order line item
      KWMENG TYPE VBAP-KWMENG, "Quantity
      D_VBELN TYPE likp-vbeln, " delivery no
      POSNR TYPE lips-posnr, " delivery item
      KUNNR TYPE LIKP-KUNNR, "ship quantity
    END OF TY_ORDERDETAILS.
    DATA:
      IT_ORDERDETAILS TYPE TABLE OF TY_ORDERDETAILS.
    

    SELECT VBAK~VBELN
      VBAK~BSTNK 
      VBAK~ERDAT
      VBAP~MATNR
      VBAP~KWMENG  
      likp~vbeln AS D_VBELN
      lips~posnr
      LIKP~KUNNR
    INTO CORRSPONDING FIELDS OF TABLE IT_ORDERDETAILS
    FROM VBAK left outer JOIN VBAP ON ( VBAK~VBELN = VBAP~VBELN )
      left outer JOIN LIPS ON ( VBAK~VBELN = LIPS~VGBEL )
      join LIKP on ( LIPS~VBELN = LIKP~VBELN )
    WHERE VBAK~ERDAT IN CR_DATE.

    Regards,

    Clemens

    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.