topic Re: internal table with same variable and one select query in Application Development and Automation Discussions

internal table with same variable and one select query

Former Member — Tue, 14 Dec 2010 14:38:34 GMT

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_ORDERDETAILS_{vbak, IT_ORDERDETAILS}bstnk, IT_ORDERDETAILS_{erdat, IT_ORDERDETAILS}matnr, IT_ORDERDETAILS_{kwmeng, IT_ORDERDETAILS}d_vbeln,IT_ORDERDETAILS_{posnr, IT_ORDERDETAILS}kunnr)

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.

I am getting error in the query.

Please suggest.

Thanks,

Rajesh

Re: internal table with same variable and one select query

Former Member — Tue, 14 Dec 2010 14:45:25 GMT

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 ( VBAK_{VBELN = VBAP}VBELN )

left outer JOIN LIPS ON ( VBAK_{VBELN = LIPS}VGBEL )

join LIKP on ( LIPS_{VBELN = LIKP}VBELN )

Hope this may be helpful.

Regards,

Sharin.

Re: internal table with same variable and one select query

dennis_bastian — Tue, 14 Dec 2010 14:58:02 GMT

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 ( VBAK_{VBELN = VBAP}VBELN )

left outer JOIN LIPS ON ( VBAK_{VBELN = LIPS}VGBEL )

join LIKP on ( LIPS_{VBELN = LIKP}VBELN )

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.

Re: internal table with same variable and one select query

tushar_shukla — Tue, 14 Dec 2010 15:20:55 GMT

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.

Re: internal table with same variable and one select query

Clemenss — Tue, 14 Dec 2010 20:59:14 GMT

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