EKPO LOEKZ field is unknown?

[Former Member]

Hi,

I am trying to extract data from EKPO tables which is not deleted yet. I tried to use the where condition in my select statements where LOEKS <> 'L' but I'm getting a syntax error stating that "Field "EKPO~LOEKZ" is unknown. It is neither in one of the specified ".

Is there any other way I can search for the non deleted PO Details records?

Please help!

Thanks

My code as below:

REPORT ZMISCY002 .

PARAMETERS :

P_FILE(300) DEFAULT '
sapdc\data\sap\pekbe.txt' LOWER CASE.

DATA MSG_TEXT(50).

TABLES: EKBE, MARA, T023T,MAKT, EKKO, EKPO.

*DECLARE INTERNAL TABLE

DATA :

XEBELN LIKE EKBE-EBELN,

XEBELP LIKE EKBE-EBELP,

XVGABE LIKE EKBE-VGABE,

XGJAHR LIKE EKBE-GJAHR,

XBELNR LIKE EKBE-BELNR,

XBWART LIKE EKBE-BWART,

XBUDAT LIKE EKBE-BUDAT,

XMENGE LIKE EKBE-MENGE,

XWRBTR LIKE EKBE-WRBTR,

XWAERS LIKE EKBE-WAERS,

XMATNR LIKE EKBE-MATNR,

XWERKS LIKE EKBE-WERKS,

XMATKL LIKE EKPO-MATKL,

XMAKTX LIKE MAKT-MAKTX,

XWGBEZ LIKE T023T-WGBEZ,

XBSART LIKE EKKO-BSART,

XLOEKZ LIKE <b>EKPO~LOEKZ</b>.

DATA : BEGIN OF ITAB_EKBE OCCURS 0,

EBELN LIKE EKBE-EBELN,

EBELP LIKE EKBE-EBELP,

VGABE LIKE EKBE-VGABE,

GJAHR LIKE EKBE-GJAHR,

BELNR LIKE EKBE-BELNR,

BWART LIKE EKBE-BWART,

BUDAT LIKE EKBE-BUDAT,

MENGE(13) type c,

WRBTR(13) type c,

WAERS LIKE EKBE-WAERS,

MATNR LIKE EKBE-MATNR,

WERKS LIKE EKBE-WERKS,

MATKL LIKE EKPO-MATKL,

MAKTX LIKE MAKT-MAKTX,

WGBEZ LIKE T023T-WGBEZ,

LOEKZ LIKE <b>EKPO~LOEKZ</b>.

DATA : END OF ITAB_EKBE.

SELECT M1~EBELN

M1~EBELP

M1~VGABE

M1~GJAHR

M1~BELNR

M1~BWART

M1~BUDAT

M1~MENGE

M1~WRBTR

M1~WAERS

M1~MATNR

M1~WERKS

M2~MATKL

M3~BSART

M2~LOEKZ

INTO

(XEBELN,

XEBELP,

XVGABE,

XGJAHR,

XBELNR,

XBWART,

XBUDAT,

XMENGE,

XWRBTR,

XWAERS,

XMATNR,

XWERKS,

XMATKL,

XBSART,

XLOEKZ

)

FROM EKBE AS M1

LEFT OUTER JOIN EKKO AS M3

ON M1_{EBELN = M3}EBELN

LEFT OUTER JOIN EKPO AS M2

ON M1_{EBELN = M2}EBELN AND M1_{EBELP = M2}EBELP

WHERE M1_{VGABE = '1' and <b>M2}LOEKZ <> 'L'.</b>

CLEAR ITAB_EKBE.

IF XBSART = 'P1' OR XBSART = 'P2' OR XBSART = 'P3'.

MOVE XEBELN TO ITAB_EKBE-EBELN.

MOVE XEBELP TO ITAB_EKBE-EBELP.

MOVE XVGABE TO ITAB_EKBE-VGABE.

MOVE XGJAHR TO ITAB_EKBE-GJAHR.

MOVE XBELNR TO ITAB_EKBE-BELNR.

MOVE XBWART TO ITAB_EKBE-BWART.

MOVE XBUDAT TO ITAB_EKBE-BUDAT.

MOVE XMENGE TO ITAB_EKBE-MENGE.

MOVE XWRBTR TO ITAB_EKBE-WRBTR.

MOVE XWAERS TO ITAB_EKBE-WAERS.

MOVE XMATNR TO ITAB_EKBE-MATNR.

MOVE XWERKS TO ITAB_EKBE-WERKS.

MOVE XMATKL TO ITAB_EKBE-MATKL.

MOVE XLOEKS TO ITAB_EKBE-LOEKZ.

*GET WGBEZ

SELECT WGBEZ INTO ITAB_EKBE-WGBEZ

FROM T023T

WHERE SPRAS = 'EN' AND MATKL = XMATKL.

ENDSELECT.

*GET MAKTX

SELECT MAKTX INTO ITAB_EKBE-MAKTX

FROM MAKT

WHERE MATNR = XMATNR.

ENDSELECT.

APPEND ITAB_EKBE.

ENDIF.

CLEAR ITAB_EKBE.

ENDSELECT.

OPEN DATASET P_FILE FOR OUTPUT IN TEXT MODE.

IF SY-SUBRC NE 0.

WRITE: 'File cannot be opened. Reason:', MSG_TEXT.

EXIT.

ENDIF.

*TRANSFER DATA

LOOP AT ITAB_EKBE.

TRANSFER ITAB_EKBE TO P_FILE.

ENDLOOP.

*CLOSE THE FILE

CLOSE DATASET P_FILE. .

[Former Member]

I don't know if it's correct or not, but the following compiles in our 4.6 C system:

REPORT ZMISCY002 .
PARAMETERS :
P_FILE(300) DEFAULT '\sapdcdatasappekbe.txt' LOWER CASE.
DATA MSG_TEXT(50).
TABLES: EKBE, MARA, T023T,MAKT, EKKO, EKPO.

*DECLARE INTERNAL TABLE
DATA :
XEBELN LIKE EKBE-EBELN,
XEBELP LIKE EKBE-EBELP,
XVGABE LIKE EKBE-VGABE,
XGJAHR LIKE EKBE-GJAHR,
XBELNR LIKE EKBE-BELNR,
XBWART LIKE EKBE-BWART,
XBUDAT LIKE EKBE-BUDAT,
XMENGE LIKE EKBE-MENGE,
XWRBTR LIKE EKBE-WRBTR,
XWAERS LIKE EKBE-WAERS,
XMATNR LIKE EKBE-MATNR,
XWERKS LIKE EKBE-WERKS,
XMATKL LIKE EKPO-MATKL,
XMAKTX LIKE MAKT-MAKTX,
XWGBEZ LIKE T023T-WGBEZ,
XBSART LIKE EKKO-BSART,
XLOEKZ LIKE EKPO-lOEKZ.


DATA : BEGIN OF ITAB_EKBE OCCURS 0,
EBELN LIKE EKBE-EBELN,
EBELP LIKE EKBE-EBELP,
VGABE LIKE EKBE-VGABE,
GJAHR LIKE EKBE-GJAHR,
BELNR LIKE EKBE-BELNR,
BWART LIKE EKBE-BWART,
BUDAT LIKE EKBE-BUDAT,
MENGE(13) type c,
WRBTR(13) type c,
WAERS LIKE EKBE-WAERS,
MATNR LIKE EKBE-MATNR,
WERKS LIKE EKBE-WERKS,
MATKL LIKE EKPO-MATKL,
MAKTX LIKE MAKT-MAKTX,
WGBEZ LIKE T023T-WGBEZ,
LOEKZ LIKE EKPO-LOEKZ.
DATA : END OF ITAB_EKBE.

SELECT M1~EBELN
M1~EBELP
M1~VGABE
M1~GJAHR
M1~BELNR
M1~BWART
M1~BUDAT
M1~MENGE
M1~WRBTR
M1~WAERS
M1~MATNR
M1~WERKS
M2~MATKL
M3~BSART
M2~LOEKZ

INTO
(XEBELN,
XEBELP,
XVGABE,
XGJAHR,
XBELNR,
XBWART,
XBUDAT,
XMENGE,
XWRBTR,
XWAERS,
XMATNR,
XWERKS,
XMATKL,
XBSART,
XLOEKZ
)
FROM EKBE AS M1
 JOIN EKKO AS M3
ON M1~EBELN = M3~EBELN
 JOIN EKPO AS M2
ON M1~EBELN = M2~EBELN AND M1~EBELP = M2~EBELP
WHERE M1~VGABE = '1' and M2~LOEKZ <> 'L'.

CLEAR ITAB_EKBE.

IF XBSART = 'P1' OR XBSART = 'P2' OR XBSART = 'P3'.

MOVE XEBELN TO ITAB_EKBE-EBELN.
MOVE XEBELP TO ITAB_EKBE-EBELP.
MOVE XVGABE TO ITAB_EKBE-VGABE.
MOVE XGJAHR TO ITAB_EKBE-GJAHR.
MOVE XBELNR TO ITAB_EKBE-BELNR.
MOVE XBWART TO ITAB_EKBE-BWART.
MOVE XBUDAT TO ITAB_EKBE-BUDAT.
MOVE XMENGE TO ITAB_EKBE-MENGE.
MOVE XWRBTR TO ITAB_EKBE-WRBTR.
MOVE XWAERS TO ITAB_EKBE-WAERS.
MOVE XMATNR TO ITAB_EKBE-MATNR.
MOVE XWERKS TO ITAB_EKBE-WERKS.
MOVE XMATKL TO ITAB_EKBE-MATKL.
MOVE XLOEKz TO ITAB_EKBE-LOEKZ.


*GET WGBEZ
SELECT WGBEZ INTO ITAB_EKBE-WGBEZ
FROM T023T
WHERE SPRAS = 'EN' AND MATKL = XMATKL.
ENDSELECT.

*GET MAKTX
SELECT MAKTX INTO ITAB_EKBE-MAKTX
FROM MAKT
WHERE MATNR = XMATNR.
ENDSELECT.

APPEND ITAB_EKBE.

ENDIF.
CLEAR ITAB_EKBE.
ENDSELECT.
OPEN DATASET P_FILE FOR OUTPUT IN TEXT MODE.
IF SY-SUBRC NE 0.
WRITE: 'File cannot be opened. Reason:', MSG_TEXT.
EXIT.
ENDIF.
*TRANSFER DATA
LOOP AT ITAB_EKBE.
TRANSFER ITAB_EKBE TO P_FILE.
ENDLOOP.
*CLOSE THE FILE
CLOSE DATASET P_FILE.

Rob

[Former Member]

Hi All,

I got it working already, thanks to your tips and inputs.

Thanks a lot!

Regards,

Cecilia

[Former Member]

Here is your code which is not having the syntax errors now...

You have to consider the following points for the LEFT OUTER JOIN:

Only a table or view may come after the JOIN operator, not another join statement.

The only logical operator allowed in the ON condition is AND.

Each comparison in the ON condition must contain a field from the right-hand table.

Comparisons in the WHERE condition must not contain a field from the right-hand table.

The ON condition must contain at least one "real" JOIN condition (a condition in which a field from tabref1 as well as from tabref2 occurs).

REPORT ZMISCY002 .

PARAMETERS :

P_FILE(300) DEFAULT '
sapdc\data\sap\pekbe.txt' LOWER CASE.

DATA MSG_TEXT(50).

TABLES: EKBE, MARA, T023T,MAKT, EKKO, EKPO.

*DECLARE INTERNAL TABLE

DATA :

XEBELN LIKE EKBE-EBELN,

XEBELP LIKE EKBE-EBELP,

XVGABE LIKE EKBE-VGABE,

XGJAHR LIKE EKBE-GJAHR,

XBELNR LIKE EKBE-BELNR,

XBWART LIKE EKBE-BWART,

XBUDAT LIKE EKBE-BUDAT,

XMENGE LIKE EKBE-MENGE,

XWRBTR LIKE EKBE-WRBTR,

XWAERS LIKE EKBE-WAERS,

XMATNR LIKE EKBE-MATNR,

XWERKS LIKE EKBE-WERKS,

XMATKL LIKE EKPO-MATKL,

XMAKTX LIKE MAKT-MAKTX,

XWGBEZ LIKE T023T-WGBEZ,

XBSART LIKE EKKO-BSART,

XLOEKZ LIKE EKPO~LOEKZ.

DATA : BEGIN OF ITAB_EKBE OCCURS 0,

EBELN LIKE EKBE-EBELN,

EBELP LIKE EKBE-EBELP,

VGABE LIKE EKBE-VGABE,

GJAHR LIKE EKBE-GJAHR,

BELNR LIKE EKBE-BELNR,

BWART LIKE EKBE-BWART,

BUDAT LIKE EKBE-BUDAT,

MENGE(13) type c,

WRBTR(13) type c,

WAERS LIKE EKBE-WAERS,

MATNR LIKE EKBE-MATNR,

WERKS LIKE EKBE-WERKS,

MATKL LIKE EKPO-MATKL,

MAKTX LIKE MAKT-MAKTX,

WGBEZ LIKE T023T-WGBEZ,

LOEKZ LIKE EKPO~LOEKZ.

DATA : END OF ITAB_EKBE.

SELECT M1~EBELN

M1~EBELP

M1~VGABE

M1~GJAHR

M1~BELNR

M1~BWART

M1~BUDAT

M1~MENGE

M1~WRBTR

M1~WAERS

M1~MATNR

M1~WERKS

M2~MATKL

M3~BSART

M2~LOEKZ

INTO

(XEBELN,

XEBELP,

XVGABE,

XGJAHR,

XBELNR,

XBWART,

XBUDAT,

XMENGE,

XWRBTR,

XWAERS,

XMATNR,

XWERKS,

XMATKL,

XBSART,

XLOEKZ

)

FROM EKBE AS M1

LEFT OUTER JOIN EKKO AS M3

ON M1_{EBELN = M3}EBELN

LEFT OUTER JOIN EKPO AS M2

ON M1_{EBELN = M2}EBELN AND M1_{EBELP = M2}EBELP

WHERE M1_{VGABE = '1' and M2}LOEKZ <> 'L'.

CLEAR ITAB_EKBE.

IF XBSART = 'P1' OR XBSART = 'P2' OR XBSART = 'P3'.

MOVE XEBELN TO ITAB_EKBE-EBELN.

MOVE XEBELP TO ITAB_EKBE-EBELP.

MOVE XVGABE TO ITAB_EKBE-VGABE.

MOVE XGJAHR TO ITAB_EKBE-GJAHR.

MOVE XBELNR TO ITAB_EKBE-BELNR.

MOVE XBWART TO ITAB_EKBE-BWART.

MOVE XBUDAT TO ITAB_EKBE-BUDAT.

MOVE XMENGE TO ITAB_EKBE-MENGE.

MOVE XWRBTR TO ITAB_EKBE-WRBTR.

MOVE XWAERS TO ITAB_EKBE-WAERS.

MOVE XMATNR TO ITAB_EKBE-MATNR.

MOVE XWERKS TO ITAB_EKBE-WERKS.

MOVE XMATKL TO ITAB_EKBE-MATKL.

MOVE XLOEKS TO ITAB_EKBE-LOEKZ.

*GET WGBEZ

SELECT WGBEZ INTO ITAB_EKBE-WGBEZ

FROM T023T

WHERE SPRAS = 'EN' AND MATKL = XMATKL.

ENDSELECT.

*GET MAKTX

SELECT MAKTX INTO ITAB_EKBE-MAKTX

FROM MAKT

WHERE MATNR = XMATNR.

ENDSELECT.

APPEND ITAB_EKBE.

ENDIF.

CLEAR ITAB_EKBE.

ENDSELECT.

OPEN DATASET P_FILE FOR OUTPUT IN TEXT MODE.

IF SY-SUBRC NE 0.

WRITE: 'File cannot be opened. Reason:', MSG_TEXT.

EXIT.

ENDIF.

*TRANSFER DATA

LOOP AT ITAB_EKBE.

TRANSFER ITAB_EKBE TO P_FILE.

ENDLOOP.

*CLOSE THE FILE

CLOSE DATASET P_FILE. .

[Former Member]

Cecillia,

User EKPO-LOEKZ instead of EKPO~LOEKZ in line numbers 25 & 44 of your code.

Cheers

Raghava

Message was edited by: Raghava M