Making an effective Select

[Former Member]

Dear Experts,

I want to select from the table REGUP only those entries where there are the same accounting documentNumber(BELNR) for the VBLNR.

How to make an effective select statement here.

Eg:-

VBLNR       Belnr

1000011     2261550005
1000012     2261550006
1000013     2261550005
1000014     2261550008

Then the select should filter out only these 2.

1000011     2261550005
1000013     2261550005

Thanks
P

[Former Member]

Hello Guys,

should I have to select everything from the table into an itab and then sort it by belegnr and loop through.

Is that a good idea?

[amy_king]

Hi Arun,

This requirement is complicated by the fact that REGUP is a cluster table so you cannot use aggregations when querying it. I wonder if you can pull your data from a similar table that is a transparent table, e.g., REGUP_CORE?

If you can find a transparent table that can substitute for REGUP, you can use the following approach to identify these records. In my system, I have no data in REGUP_CORE, so I'm using transparent table BKPF to show the example.

SELECT belnr blart
               FROM bkpf
               INTO TABLE lt_itab
               WHERE blart IN ( SELECT blart
                                                           FROM bkpf
                                                           GROUP BY blart
                                                           HAVING COUNT( DISTINCT belnr ) > 1 ).

The subquery identifies BLART values which appear for more than one BELNR. Then the main query selects BELNR and BLART records for that set of reused BLART values.

If you can identify a transparent table for your query, your query would then look like...

SELECT vblnr belnr
               FROM transparent_table
               INTO TABLE lt_itab
               WHERE belnr IN ( SELECT belnr
                                                           FROM transparent_table
                                                           GROUP BY belnr
                                                           HAVING COUNT( DISTINCT vblnr ) > 1 ).

Cheers,

Amy

[Former Member]

Thanks Amy.I will try this one out tomorrow.

As the REGUP table contains over 3 million entries,I have to do some filtering.

One question though. If I have to make a join with the REGUH table,Can I modify the code as below.

SELECT vblnr belnr

               FROM transparent_table

               INTO TABLE lt_itab             

               WHERE belnr IN ( SELECT belnr

                                                           FROM transparent_table

                                                           INNERJOIN REGUH

                                                           ON REGUH~vbeln = REGUP~vbeln

                                                           where REGUP~LAUFD in so_datum // where so_datum is a select options.

                                                           GROUP BY belnr

                                                           HAVING COUNT( DISTINCT vblnr ) > 1 ).

[amy_king]

Hi Arun,

In the subquery, since you are joining REGUH with REGUP, I assume you mean to use REGUP as the "transparent_table", however you cannot use REGUP with aggregations (GROUP BY ... HAVING ...) since REGUP is a cluster table.

Cheers,

Amy

[Former Member]

Thank You.