topic Re: problem with select....where IN statement in Application Development and Automation Discussions

problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 11:40:21 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 11:47:18 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 11:54:43 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 12:03:32 GMT

use the syntax

tables cosp.

ranges: s_kostl for cosp-objnr.

it will work fine.

Thanks and Regards,

Dinesh.

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 12:25:11 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 12:25:45 GMT

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

Re: problem with select....where IN statement

RaymondGiuseppi — Wed, 24 Apr 2013 12:27:07 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 12:27:54 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 12:32:34 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 12:35:40 GMT

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

Re: problem with select....where IN statement

Clemenss — Wed, 24 Apr 2013 14:19:25 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 14:43:27 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 14:45:44 GMT

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

Re: problem with select....where IN statement

RaymondGiuseppi — Wed, 24 Apr 2013 14:52:45 GMT

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

Regards,

Raymond

Re: problem with select....where IN statement

RaymondGiuseppi — Wed, 24 Apr 2013 15:03:23 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 15:07:36 GMT

thanks
A lot

Regards

Miguel

Re: problem with select....where IN statement

kabil_g — Wed, 24 Apr 2013 15:14:37 GMT

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

Re: problem with select....where IN statement

Former Member — Wed, 24 Apr 2013 15:57:12 GMT

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

Re: problem with select....where IN statement

Former Member — Fri, 05 Jul 2013 09:46:33 GMT

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.

Kim

Re: problem with select....where IN statement

former_member216769 — Fri, 05 Jul 2013 10:44:58 GMT

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.