SAP Connector: Fetching data from BKPF and BSEG using JOIN

[jesper_bagge]

Hi,

I noticed today that the usual JOIN statement doen't seem to work when calling the tables BKPF and BSEG.

The following SQL Statement

SQL Select
 BKPF~BELNR,
 BKPF~BUDAT,
 BKPF~GJAHR,
 BKPF~BUKRS,
 BSEG~SHKZG,
 BSEG~HKONT,
 BSEG~BUZEI,
 BSEG~DMBTR
from BKPF JOIN BSEG ON BKPF~BELNR = BSEG~BELNR AND BKPF~GJAHR = BSEG~GJAHR AND BKPF~BUKRS = BSEG~BUKRS
;

returns an error.

Im using version 5.0.6526.0 of the SAP Connector.

Using the same logic when fetching data from other tables work fine. It seems to only be when i call the tables BKPF and BSEG.

Any thoughts on why I get an error? Bug perhaps?

[danielrozental]

It's a SAP restriction related to BSEG being a cluster table (not much of an expert, just repeating what I've heard).

You should try something like SQL SELECT BELNR FROM BSEG WHERE (BELNR IN (SELECT BELNR FROM BKPF WHERE CPUDT = '20070819'));

[jbeierschmitt]

No issues that I know of with BKPF/BSEG - try to extract them separately perhaps?

Also, you may find that GLPCA & GLPCT are more useful tables for Finance dashboards...

[jesper_bagge]

@Daniel

I'm not sure that the connector can handle such "complex" SQL queries, since I know from earlier experiences that it cannot even handle a simple ALIAS command. Anyhow, your proposed query returns the transactions registered on a single date. Not sure what I could whip up with that since my customer wants a financial application spanning over atleast 3 years 😉

@jbeierschmitt

Yes, a simple extraction of both tables and the joining of relevant data inside QV has been my previous method, and will have to make do. The GLPCA and GLPCT contains to my knowledge only aggregated data. I always go for the raw stuff, since one of the true strengths of QV is the ability to present data at an aggregated level to the user, but keeping the original transactions for data-drilling.

Cheers!

[#5 · Accepted solution]

Hi Jesper,

It's not possible, even in Abap code, to make a join on BSEG and BKPF. It's, as you wrote, a cluster table.

It's a joined table from bkpf,bsid,bsad,bsis,bsik,bsak,bset (and other)

For information, GLPCA and GLPCT are controlling table. BSEG,BKPF,BSID,BSAD, and other, are accounting or financial table.

regards

serge

[#6]

a link if you want the SAP table

or http://www.4shared.com/file/108322076/e2083/sap-tables.html you download the zip file
this is sap-tables.exe it's relation between all sap tables

same thing on PDF

www.abap.es/Descargas/TAB%20-%20Relacion%20de%20las%20tablas%20por%20modulos.PDF

[jesper_bagge]

Thank you sdesland. I did not know that BSEG was actually a result of a join of multiple other tables.

[#8]

hi jesper

So i said it's a join of table it's to simplify.

BSEG is a cluster/pool table

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f083446011d189700000e8322d00/content.htm

you can see what is a cluster or a pool table in SAP.For your information,You can't read pool or cluster table directly on the database with an ODBC or other connection SQL from outside SAP. You have to use a sap connector.Other table, transparent table in SAP, no problem.

regards

serge

[pablolabbe]

You can´t use subquerys to select data from Cluster Tables like BSEG. I sugest you to create a loop that read data in buckets of 300 DocumentNumber (BELNR) from BKPF in a date range. Then use a the following SELECT to query BSEG:

SELECT BELNR, FIELD2,FIELD3, .....

FROM BSEG

WHERE GJAHR = <YEAR> AND BUKRS = <COMPANY GROUP> AND (BELNR = 30204 OR BELNR = 2049229593 OR BELNR = 0430204 ... OR BELNR = 305030)

You need to put GJAHR and BUKRS in the where clause to use primary index to achieve read performance.

[jesper_bagge]

Thank you for your input Pablo.

The user sdesland answered this topic quite some time ago.

An even simpler solution is an incremental load of both BSEG and BKPF where GJAHR and BUKRS serves as key fields. I can do the join later in QlikView.

No loops, no buckets, no worries