topic Re: Making an effective Select in Application Development and Automation Discussions

Making an effective Select

Former Member — Mon, 19 Nov 2012 08:54:30 GMT

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

Re: Making an effective Select

Former Member — Mon, 19 Nov 2012 18:29:51 GMT

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?

Re: Making an effective Select

amy_king — Mon, 19 Nov 2012 21:02:45 GMT

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

Re: Making an effective Select

Former Member — Mon, 19 Nov 2012 22:13:36 GMT

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 ).

Re: Making an effective Select

amy_king — Tue, 20 Nov 2012 15:31:04 GMT

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

Re: Making an effective Select

Former Member — Thu, 22 Nov 2012 12:44:46 GMT

Thank You.