topic Re: INNER JOIN in Application Development and Automation Discussions

INNER JOIN

Former Member — Sun, 19 Nov 2006 10:32:37 GMT

Hello

I have 2 tables to select from, bkpf (header) bseg (item),

Due to a lot of data I would like to create a view or inner join

From this 2 tables, however because bseg is a Cluster table it can't be done.

Any suggestion?

Thank Emmanuel

Re: INNER JOIN

anversha_s — Sun, 19 Nov 2006 10:36:34 GMT

hi,

you can do like this

1. FOR ALL ENTRIES.

2. It is recommended that

first select entries from only one table

(say, BKPF),

3. After that, using the internal table,

FOR ALL ENTRIES, and BSEG table,

select the relevant entries from BSEG table.

4. Performance wise, this will be very fast.

Regards

Anver

if hlped pls mark points

Re: INNER JOIN

Former Member — Sun, 19 Nov 2006 10:41:02 GMT

Thank you Anver

But the statement 'FOR ALL ENTRIES'

Takes a lot of times (more than 24 hours for millions of records), and this

Is the reason that I wanted something else?

Regards Emmanuel

Re: INNER JOIN

Former Member — Sun, 19 Nov 2006 10:43:19 GMT

Just as you wrote it can't do a join using BSEG because is a cluster table.

U can replace the join with FOR ALL ENTRIES options if you can create a WHERE condtion only for BKPF table:

DATA: T_BKPF LIKE STANDARD TABLE OF BKPF WITH HEADER LINE.
DATA: T_BSEG LIKE STANDARD TABLE OF BSEG WITH HEADER LINE.

SELECT * FROM BKPF INTO TABLE T_BKPF WHERE BUKRS = .....
IF SY-SUBRC = 0.
   SELECT * FROM BSEG INTO TABLE T_BSEG
         FOR ALL ENTRIES IN T_BKPF
                           WHERE BUKRS = T_BKPF-BUKRS
                                 AND BELNR = T_BKPF-BELNR
                                 AND GJAHR = T_BKPF-GJAHR.
ENDIF.

If you need to select only a certain kind of item you can use the secondary index tables:

- BSIS/BSAS Open/Cleared items for G/L account

- BSID/BSAD Open/Cleared items for Customer account

- BSIK/BSAK Open/Cleared items for Vendor account

These are transparent table so you can use them in a join.

Max

Re: INNER JOIN

Former Member — Sun, 19 Nov 2006 10:48:04 GMT

Thank you max

using

- BSIS/BSAS Open/Cleared items for G/L account

- BSID/BSAD Open/Cleared items for Customer account

- BSIK/BSAK Open/Cleared items for Vendor account

is the best solution.

Thank you Emmanuel

Re: INNER JOIN

anversha_s — Sun, 19 Nov 2006 10:48:45 GMT

Hi,

You can use for all entries and put some where condition as MAX told.

otherwise u can use bsis/bsas, bsik/bsak, bsid/bsad

-> here're fields of bkpf and bseg for different kinds of accounts

Regards

Anver

pls mark all helpful answers

Re: INNER JOIN

Former Member — Sun, 19 Nov 2006 18:13:53 GMT

What are your selection criteria?

Rob