SELECT query based on two internal tables.

[aminpranal]

Hello Folks,

I am facing an issue due to which I need to make an improvisation on the below SELECT statement:

SELECT objectclas objectid changenr tabname tabkey
             fname chngind value_new value_old
             FROM cdpos INTO TABLE lt_changelogs
             FOR ALL ENTRIES IN lt_chglog_rules
             WHERE objectclas = lt_chglog_rules-objectclas
               AND changenr   IN lt_r_changenr
               AND tabname    = lt_chglog_rules-tabname
               AND fname      = lt_chglog_rules-fname
               AND value_new  = lt_chglog_rules-newval
               AND value_old  = lt_chglog_rules-oldval.

The above query works perfectly fine when the number of entries in the range table lt_r_changenr is less.

Off late we are noticing more than 200,000 records in this range table which ends up in the dump attached

On researching the above dump, the most common solution available is to use FOR ALL ENTRIES statement on the range table instead of the IN operator but it is already used for another internal table in the query.

Also we are avoiding using SLEECT statement inside a LOOP due to performance issues and bad coding practices.

Any improvisation suggestions would be appreciated that could help mitigate this issue.

Thanks in advance!

[FredericGirod]

if changelog number comes from CDHDR why didn't you make an INNER JOIN instead of a ranges ?

second option, I have created several time a method that convert ranges to reduce the size :

instead of I EQ 1 ... 2 ... 3... 4 ... 6 it gives you I BT 1 4 I EQ 6 ... sometimes it works also

[aminpranal]

frdric.girod

The second option sounds better as we are trying to do minimal changes as this report runs as a job and has dependencies associated.
Do we have any FM or method that can help to convert the ranges to minimize the size?

[Sandra_Rossi]

If you don't want to take any risk, you may also split lt_chglog_rules into smaller groups of lines and repeat the SELECT for each group, append lines, and after the loop do a "sort" and "delete adjacent duplicates" so that to mimic the DISTINCT behavior of FOR ALL ENTRIES.

[FredericGirod]

pretty sure there is a cds view on these tables, did you check this option ?

[aminpranal]

frdric.girod

This a legacy system where the code is present and not a HANA system, so CDS views are not there.

[ThorstenHoefer]

Hi Pranal,

instead of using the internal table lt_chglog_rules, you should consider to join the tables.
In newer released (7.5.x) you can select also directly from the internal table.

select distinct a~*
from cdpos as a
inner join @lt_chglog_rules as b 
  on b~objectclas = a~objectclas
 AND b~tabname    = a-tabname
 AND b~fname      = a-fname
 AND b~value_new  = a-newval
 AND b~value_old  = a-oldval
where a~changenr   IN lt_r_changenr
into table @data(lt_data)

[aminpranal]

thorstenhoefer

Thanks for your valuable suggestion.
In my case th range table lt_r_changenr has close to 200,000 records which leads to the RSQL dump mentioned.
Will this SELECT query suggested by you take care of the dump then?

[ThorstenHoefer]

Hi aminpranal,

select for all entries generate a large sql string which is executed on the datbase.

This can be avoid by joining two tables.

[FredericGirod]

Why did everybody do a : CDPOS as A ??

This is human speaking A ? Do you understand what it means A without the CDPOS ?

Why didn't you write : CDPOS as Change_Item ?

[ThorstenHoefer]

"as a" is only an alias. If you select from an internal table @lt_chglog, you need to use an alias.

You can use anything else as an alias, or you can remove the alias for table cdpos.

It doesn't matter.

[aminpranal]

thorstenhoefer frdric.girod

The netweaver version for the system is 7.0.
So I think the SELECT ...JOIN on internal table will not be supported.

Do we have an alternative here?

Thanks.

[FredericGirod]

inner join works in sap R/3 4.5 sure, and certainly before

[ThorstenHoefer]

inner joins works of course also before, but the new feature is to select from the internal table @lt_chglog_rules.

If you can join the original table of the source from lt_chglog_rules you will be fine.

How do you fill the internal table lt_chglog_rules?

[aminpranal]

thorstenhoefer

lt_chglog_rules gets filed from rules defined in BRF+.

We are coming up with something like below to solve the dump issue.

LOOP AT lt_changenr INTO lwa_changenr.
    LOOP AT lt_chglog_rules INTO lwa_chglog_rules.
      MOVE-CORRESPONDING lwa_chglog_rules TO lwa_chglog_rules_chgnr.
      lwa_chglog_rules_chgnr-changenr = lwa_changenr-changenr.
      APPEND lwa_chglog_rules_chgnr TO lt_chglog_rules_chgnr.
      clear lwa_chglog_rules_chgnr, lwa_chglog_rules.
    ENDLOOP.
    clear lwa_changenr.
  ENDLOOP.


* and then the select query on this new internal table having same structure as lt_chglog_rules with an 
* additional field changenr
* Now the SELECT query changes by eliminating the RANGE table
SELECT objectclas objectid changenr tabname tabkey
       fname chngind value_new value_old
       FROM cdpos INTO TABLE lt_changelogs
       FOR ALL ENTRIES IN lt_chglog_rules_chgnr
       WHERE objectclas = lt_chglog_rules_chgnr-objectclas
       AND changenr    = lt_chglog_rules_chgnr-changenr
       AND tabname    = lt_chglog_rules_chgnr-tabname
       AND fname      = lt_chglog_rules_chgnr-fname
       AND value_new  = lt_chglog_rules_chgnr-newval
       AND value_old  = lt_chglog_rules_chgnr-oldval.

This will eliminate the need for the RANGE table and prevent the RSQL dump.

[former_member598787]

Hello,

Range tables are usually designed for a smaller input set. If I recall correctly it should be in range of 1000 or something. If the the entries are more you would get the dump as mentioned in your question. I would suggest to use FOR ALL ENTRIES IN instead.

for your Query where you are using lt_r_changenr there if possible create a separate table with two fields, object class and change no. then use it in for all entries in.
Also another suggestion would be to check your query once, I think you might also have a performance impact.

Regards,

Juby

[matt]

CDS views and HANA are distinct, independent technologies. I'm using a system right now that's not HANA but has CDS views.