problem with select....where IN statement

[Former Member]

Hi,

i have the following select-statement in my z-abap:

  DATA: s_kostl    TYPE RANGE OF cosp-objnr,

  DATA: s_kstar    TYPE RANGE OF kstar,

.

.

.

  SELECT (lv_feldname) INTO lv_betrag

                  FROM coss WHERE lednr = '00' AND

                             objnr IN s_kostl AND

                             gjahr = gjahr AND

                             kstar IN s_kstar AND

                             wrttp = '04' AND

                             versn = '000'.

now i get a dump, because s_kostl and/or s_kstar contains about 11.000 entries (DBIF_RSQL_INVALID_RSQL).

how can avoid this ? or how i have to 'rewrite' my SQL statment that the ranges-tabs s_kostl AND s_kstar can be processed

correctly ?

br, Martin

[Former Member]

Hi,

     How actually are you populating the range table s_kostl and s_kstar? Have the SIGN and OPTION values for each entry provided with correct values?

~Athreya

[Former Member]

yes, sign and option values are right !  i already have checked this twice. i fill the s_tables in my program.

so the table look like this...........

I BT 10000 10002

I BT 10005 10005

etc...................................................

but anyway, the problem is the LIMIT of this ranges-tabs i think ???

br Martin

[Former Member]

use the syntax

tables cosp.

ranges: s_kostl for cosp-objnr.

it will work fine.

Thanks and Regards,

Dinesh.

[Former Member]

ok, now i have tried it and as i said: the dump is still there !

[Former Member]

Martin,

     Note https://service.sap.com/sap/support/notes/13607 talks about the error that you have faced.

     See if it helps to solve your problem.

~Athreya

[RaymondGiuseppi]

You have to rewrite as there is a maximal size allowed by open-sql and actual database for the sql generated statement, so either

- break your ranges in smaller range (Like 2 loops on both ranges to create sub-ranges of less than 1000 records, perform some test to find better size acceptd for your database) Of course there must only be "I" include records in the range, and you may generate duplicates.

- use a FOR ALL ENTRIES (which will adapt its size, but usually create a shorter list than allowed) but you can do that for only one range, there bus be only "I/REQ" list records in the range.

You could also wrap the SELECT in a TRY/ENDTRY block CATCHing such errors, that would display the actual error text.

TRY.
  SELECT...
  CATCH cx_sy_open_sql_db into oref.
  lv_text = oref->get_text( ).
  MESSAGE...
ENTRY.

Regards,

Raymond

[Former Member]

sorry, but this will not work for me.

the s_kostl and s_kstar table contains BT and EQ mixed together.

I EQ 2000000

I BT 2000001 20000002

I BT 3000000 30000001

I EQ 4000000

and so on...........

"splitting the statment" is no good option, because i NEVER know how much data there

will be in the future.

i don't understand SAP here that this problem is there for years now. why is this limit there ? stupid !

br, Martin

[Former Member]

Hello Raymond Giuseppi, one question, hwo to post the code as you did. Because I'm using {code} and it doesnt work...did it change? Please let me know how to do it.

I'm using {} with the word code inside.

Regards

Miguel

[RaymondGiuseppi]

The old {code} tags no longer valid, now click "Use advanced editor", then click on , Syntax Highlighting, Plain.

Regards,

Raymond

[RaymondGiuseppi]

You can break without knowledge of size (provided you forbide Exclusion record via SELECT_OPTIONS_RESTRICT.

refresh subrange1.
LOOP at range1 ,  
  append range1 to subrange1, 
  at last range1 or when 1000 records in subrange1, 
    refresh subrange2.  
    LOOP AT range2 , 
      append range2 to subrange2.
      at last range2 or when 1000 records in subrange2, 
        SELECT APPENDING WHERE IN subrange1 AND IN subrange2. 
        refresh subrange2.
    ENDLOOP.
    refresh subrange1.
  ENDLOOP.
  SORT
  DELETE ADJACENT DUPLICATES

Regards,

Raymond

[Former Member]

thanks
A lot

Regards

Miguel

[Former Member]

Hi martin

SELECT (lv_feldname) INTO lv_betrag

                  FROM coss WHERE lednr = '00' AND

                             s_kostl IN objnr AND

                             gjahr = gjahr AND

                               s_kstar IN kstar AND

                             wrttp = '04' AND

                             versn = '000'.

actually whenever you use IN keyword , make sure you write the field declared in the program first followed by IN and then the field you want to check with the database table .

close the thread if this helps.

regards

vaibhav

[Former Member]

sorry, but you are completly wrong with that !

you cannot put the range table first !!! and than the database-field. why do you think this works ?

br, Martin

[Clemenss]

Hi Martin,

it is really bad that SAP has let go down this forum so far by introducing facebook-like nonsense that doesn't help or work at least.

The result is that people with pure knowledge feel encouraged to post their nonsense here - just because  nobody else will listen 🙂

Well, there is a restriction for the size of an SQL statement for the database. I think the old 16k limit has been doubled by oracle to 32k. Your range tables exceed the limit.

The easiest way will be to use FOR ALL ENTRIES syntax for one range table and delete itab where for the second range table.

I could not change your code because it is incomplete.

Regards

Clemens

[Former Member]

Hello.

I guess that the problem is not the limit or ranges, it is on the select statement. check it

It should be select single

yours:

{code}

SELECT (lv_feldname) INTO lv_betrag

                  FROM coss WHERE lednr = '00' AND

                             objnr IN s_kostl AND

                             gjahr = gjahr AND

                             kstar IN s_kstar AND

                             wrttp = '04' AND

                             versn = '000'.

{code}

correct:

{code}

SELECT SINGLE (lv_feldname) INTO lv_betrag

                  FROM coss WHERE lednr = '00' AND

                             objnr IN s_kostl AND

                             gjahr = gjahr AND

                             kstar IN s_kstar AND

                             wrttp = '04' AND

                             versn = '000'.

{code}

Try doing that change. Also, check frist without using LV_feldname, use the name of the field that u want to select, once u get the correct result change it back.

Regards

Miguel

[kabil_g]

Hi Martin Svik,

                    when internal table exceed the limit .   It will go to dump .A possible way of dealing this is to increase the memory limit in Application server. This is usually done by BASIS.

Regards,

Kabil

[Former Member]

Hello to all,

thank you for the many responses ! now i have solved the problem with the dump, but now i have a big performance problem: now i do it that way:

SELECT * FROM cosp INTO TABLE lt_cosp

           WHERE lednr = '00' AND

                          gjahr = gjahr AND

                          wrttp = '04' AND

                          versn = '000'.

  DELETE lt_cosp WHERE NOT objnr IN s_kostl AND NOT kstar IN s_kstar.

  LOOP AT lt_cosp.

    CONCATENATE 'lt_cosp-WKG' periode INTO lv_feldname.

    ASSIGN (lv_feldname) TO <betrag>.

    MOVE lt_cosp-objnr+6(10) TO ls_summen-kostl.

    MOVE lt_cosp-kstar       TO ls_summen-kstar.

    MOVE <betrag>            TO ls_summen-betrag.

    COLLECT ls_summen INTO lt_summen.

  ENDLOOP.

The big performance issue is the DELETE statement (bolded). In s_kostl i have about 11.000 entries and in s_kstar about 3000. The internal table lt_cosp has about 315.000 entries in my test.

I have debugged the code and the DELETE statement takes about 6 minutes or more to be finished, everything else works fine in performance.

so why does this delete take so much time ? with loop at ........... where objnr IN s_kostl....... it is exactly the same or longer in runtime.

br, Martin

[former_member216769]

Hi Martin,

DELETE will take time as it will check the entire table for valid data.

try this to perform delete,

LOOP at IT_COSP ASSIGNING <lfs_cosp>.

if <lfs_cosp>-objnr not in s_kostl.

     clear one key field of table.

elseif <lfs_cosp>-kstar not in s_kstar.

      clear one key field table.

endif.

End loop.

DELETE it_cosp where "above key field is initial".

Regards,

M Nair

[former_member216769]

FIELD-SYMBOLS: <lfs_cosp> type cosp.

SELECT * FROM cosp INTO TABLE lt_cosp

           WHERE      lednr = '00' AND

                          gjahr = gjahr AND

                          wrttp = '04' AND

                          versn = '000'.

* ASSUMING OBJNR IS YOUR KEY FIELD.

LOOP AT IT_COSP ASSIGNING <LFS_COSP>.

     IF <LFS_COSP>-OBJNR NOT IN S_KOSTL

               OR <LFS_COSP>-KSTAR NOT IN S_KSTAR.

          CLEAR <LFS_COSP>-OBJNR.

     ENDIF.

ENDLOOP.

DELETE IT_COSP WHERE OBJNR IS INITIAL.

[Former Member]

Still not answered? The problem most likely has been solved, but here's the way to do it. It has already been mentioned by Raymond Guiseppi, but perhaps it drowned in the majority of different answers.

The reason your program dumped is that a RANGE may only contain approx. 2000 entries (not an exact number, it is a bit lower). If there are too many records in the RANGE SAP isn't able to explain the statement as e.g. seen when you use the ST05 Peformance Analyzer where the SELECT statement is split into single value references.

FOR ALL ENTRIES has replaced RANGE in selection, as recommended by SAP when dealing with large restrictions on selection. Make a table containing e.g. table GT_KOSTL with fields KOSTL_LOW and KOSTL_HIGH, fill it with the wanted values (for single values fill both fields with the value), and the use the FOR ALL ENTRIES option.

You can then in your SELECT state FOR ALL ENTRIES IN GT_KOSTL, and in your WHERE that KOSTL >= GT_KOSTL-KOSTL_LOW AND KOSTL <= GT_KOSTL-KOSTL_HIGH.

This should do the trick.

BR

Kim

[former_member216769]

Hi Martin,

Try the below code,

* Create range type

TYPES: BEGIN OF TY_RNG,

                    SIGN TYPE C,

                    OPTION(2) TYPE C,

                    LOW TYPE KOSTV,

                    HIGH TYPE KOSTV,

              END OF TY_RNG,

               BEGIN OF TY_RNG,

                    SIGN TYPE C,

                    OPTION(2) TYPE C,

                    LOW TYPE KSTAR,

                    HIGH TYPE KSTAR,

              END OF TY_RNG.

DATA: RT_KOSTL TYPE TABLE OF TY_RNG,

            RT_KSTAR TYPE TABLE OF TY_RNG.

* Pass the range table entries to custom  table

APPEND LINES OF S_KOSTL TO RT_KOSTL.

APPEND LINES OF S_KSTAR TO RT_KSTAR.

* Use the above tables in your select query.

SELECT (lv_feldname) INTO lv_betrag

                  FROM coss WHERE lednr = '00' AND

                             objnr IN s_kostl AND

                             OBJNR IN RT_KOSTL AND

                             gjahr = gjahr AND

                             kstar IN s_kstar AND

                              KSTAR IN RT_KSTAR AND

                             wrttp = '04' AND

                             versn = '000'.

This might work for your requirement but it may effect your performance.

If the entries are much more then fetch relevant data into internal table and then delete entries which are not in S_KOSTL and S_KSTAR.

Regards,

M Nair.

[Clemenss]

... as Martin posted and commented the question on Apr 24, it is very urgent to continue now even without understanding the original trouble 🙂

By the way, Manikandan D Nair, did you ever come across the correct ABAP declaration for a range table?

DATA rtab {TYPE RANGE OF type}|{LIKE RANGE OF dobj}

might save you a lot of time and typing errors in the future.

Regards,

Clemens

[former_member216769]

Hey Clemens,

For your kind information I have not declared range table I have declared an internal table of type like range table.

I think you may know, an range table declared by your code

" DATA rtab {TYPE RANGE OF type}|{LIKE RANGE OF dobj} " with more than 2000 entries will give dump if it is been used for fetching data from DB.

In my code I have declared Internal table like range table type. In this way the internal table can be used as like range table and it also increases the data limit from 2000 to around 15000 while fetching data.

And I am well aware for ABAP syntax and coding.

Kindly first check and for any further clarification do let me know.

Regards

M Nair

[Former Member]

Hi,

In that case it should be better to use the 'for all entries in' but you wont profit from the 'range' option for exclusion, etc. Depending on the scenario it should be ok, otherwise you should split your select and call it multiple times.

Regards,

David.

[ThomasZloch]

Late to the party, but here is in a nice summary.

Thomas