12-14-2010 2:38 PM
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
12-14-2010 2: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.
12-14-2010 2: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.
12-14-2010 3: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.
12-14-2010 8: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 <> 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