topic Re: SELECT query based on two internal tables. in Application Development and Automation Discussions

SELECT query based on two internal tables.

aminpranal — Tue, 12 Oct 2021 04:48:11 GMT

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!

Re: SELECT query based on two internal tables.

FredericGirod — Tue, 12 Oct 2021 04:59:25 GMT

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

Re: SELECT query based on two internal tables.

aminpranal — Tue, 12 Oct 2021 05:13:48 GMT

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?

Re: SELECT query based on two internal tables.

Sandra_Rossi — Tue, 12 Oct 2021 05:33:59 GMT

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.

Re: SELECT query based on two internal tables.

FredericGirod — Tue, 12 Oct 2021 05:41:40 GMT

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

Re: SELECT query based on two internal tables.

aminpranal — Tue, 12 Oct 2021 05:49:10 GMT

frdric.girod

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

Re: SELECT query based on two internal tables.

ThorstenHoefer — Tue, 12 Oct 2021 06:10:15 GMT

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)

Re: SELECT query based on two internal tables.

aminpranal — Tue, 12 Oct 2021 06:17:41 GMT

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?

Re: SELECT query based on two internal tables.

ThorstenHoefer — Tue, 12 Oct 2021 06:29:24 GMT

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.

Re: SELECT query based on two internal tables.

former_member598787 — Tue, 12 Oct 2021 06:31:08 GMT

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

Re: SELECT query based on two internal tables.

FredericGirod — Tue, 12 Oct 2021 06:36:29 GMT

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 ?

Re: SELECT query based on two internal tables.

ThorstenHoefer — Tue, 12 Oct 2021 06:46:41 GMT

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

Re: SELECT query based on two internal tables.

matt — Tue, 12 Oct 2021 07:06:04 GMT

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

Re: SELECT query based on two internal tables.

aminpranal — Tue, 12 Oct 2021 07:43:17 GMT

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.

Re: SELECT query based on two internal tables.

FredericGirod — Tue, 12 Oct 2021 08:10:01 GMT

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

Re: SELECT query based on two internal tables.

ThorstenHoefer — Tue, 12 Oct 2021 09:45:35 GMT

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?

Re: SELECT query based on two internal tables.

aminpranal — Tue, 12 Oct 2021 11:10:37 GMT

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.