Problem Joining BKPF and BSEG

[Former Member]

Hello,

I've got the following Problem:

I created a Programm with the following Code:

EXEC SQL PERFORMING loop_output.
 SELECT b1.BLART, b1.BELNR, b1.BUKRS, b1.BUDAT, b1.XBLNR, b1.STGRD
 INTO :wa FROM BKPF b1
 INNER JOIN
	(
	 SELECT BLART, BUKRS, XBLNR
	 FROM BKPF
	 WHERE BUKRS = :BUKREIS
	 AND (BUDAT >= :VON OR :VON = '00000000')
	 AND (BUDAT < :BIS OR :BIS = '00000000')
	 AND BLART = 'RG'
	 AND LENGTH(TRIM(XBLNR)) > 1
	 AND STGRD = ' '
	 GROUP BY BLART, BUKRS, XBLNR
	 HAVING COUNT(*) > 1
	 ) b2
 ON 1=1
 AND b1.BLART = b2.BLART
 AND b1.BUKRS = b2.BUKRS
 AND b1.XBLNR = b2.XBLNR
 ORDER BY b1.BLART, b1.BELNR, b1.BUKRS, b1.BUDAT, b1.XBLNR
ENDEXEC.

I want to add another join to the Table BSEG. After adding the JOIN an Error occurs somthing like "There is a object (Table, View Index etc.), that does not exist in the Database. ". So I wrote a litte Test:

REPORT ZFI_TEST.

Data: BEGIN OF wa,
	 BUKRS TYPE BSEG-BUKRS,
	 BELNR TYPE BSEG-BELNR,
 END OF wa.

EXEC SQL PERFORMING loop_output.
 SELECT b1.BUKRS, b1.BELNR
 INTO :wa
 FROM bkpf b1
ENDEXEC.

FORM loop_output.
 write: / wa-BELNR, ' ',
 wa-BUKRS.

ENDFORM.

If I referenz to the Table BKPF everyting works fine. Simply changing the Tablename to BSEG gets me an Error.

What do I have to do to JOIN the BSEG-Table or how can I solve the Problem using ABAP Using a SELFJOIN by Grouping ONLY the SUBSELECT.

Thanks.

Joachim

[Former Member]

Hello,

I've got the following Problem:

I created a Programm with the following Code:

EXEC SQL PERFORMING loop_output.
 SELECT b1.BLART, b1.BELNR, b1.BUKRS, b1.BUDAT, b1.XBLNR, b1.STGRD
 INTO :wa FROM BKPF b1
 INNER JOIN
	(
	 SELECT BLART, BUKRS, XBLNR
	 FROM BKPF
	 WHERE BUKRS = :BUKREIS
	 AND (BUDAT >= :VON OR :VON = '00000000')
	 AND (BUDAT < :BIS OR :BIS = '00000000')
	 AND BLART = 'RG'
	 AND LENGTH(TRIM(XBLNR)) > 1
	 AND STGRD = ' '
	 GROUP BY BLART, BUKRS, XBLNR
	 HAVING COUNT(*) > 1
	 ) b2
 ON 1=1
 AND b1.BLART = b2.BLART
 AND b1.BUKRS = b2.BUKRS
 AND b1.XBLNR = b2.XBLNR
 ORDER BY b1.BLART, b1.BELNR, b1.BUKRS, b1.BUDAT, b1.XBLNR
ENDEXEC.

I want to add another join to the Table BSEG. After adding the JOIN an Error occurs somthing like "There is a object (Table, View Index etc.), that does not exist in the Database. ". So I wrote a litte Test:

REPORT ZFI_TEST.

Data: BEGIN OF wa,
	 BUKRS TYPE BSEG-BUKRS,
	 BELNR TYPE BSEG-BELNR,
 END OF wa.

EXEC SQL PERFORMING loop_output.
 SELECT b1.BUKRS, b1.BELNR
 INTO :wa
 FROM bkpf b1
ENDEXEC.

FORM loop_output.
 write: / wa-BELNR, ' ',
 wa-BUKRS.

ENDFORM.

If I referenz to the Table BKPF everyting works fine. Simply changing the Tablename to BSEG gets me an Error.

What do I have to do to JOIN the BSEG-Table or how can I solve the Problem using ABAP Using a SELFJOIN by Grouping ONLY the SUBSELECT.

Thanks.

Joachim

[ankurch]

You should Take BELNR, BUDAT & BUKRS as key fields.

[ThomasZloch]

What is your DBMS?

Why are you using Native SQL and not Open SQL?

[pokrakam]

Shouldn't it be

SELECT ... FROM BKPF AS b1.

Edit: No ignore me, missed the 'EXEC SQL' bit. I'd echo Thomas's question: Why native, and try using the same with Open SQL.

I suspect your issue has to do with BSEG being a cluster table and thus stored differently at the DB level. If the same statement works with openSQL then that's definitely the issue.

Edit 2:

I don't have a suitable system to check, but you can always go to

SE11 -> Utilities -> Database Object -> Display

To see what BSEG looks like at the DB level.

[Sandra_Rossi]

Agreed.

BSEG is a "clustered table" (search the forum for more information), that means it doesn't exist in the database. The actual table is RFBLG if I remember well, but the attribute fields are compressed by SAP algorithm.

So, you WON'T BE ABLE to access them using native SQL. Instead, use Open SQL.

PS: many fields of BSEG are duplicated in index tables BSID, BSIS, BSIK, BSAD, …, which are not clustered tables.