

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
SAP Connector: Fetching data from BKPF and BSEG using JOIN
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?
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'));


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you sdesland. I did not know that BSEG was actually a result of a join of multiple other tables.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- « Previous Replies
-
- 1
- 2
- Next Replies »