09-22-2005 8:37 AM
hello,
I have a runtime during selection from database table due to large ranges in where condition. for example:
select * from cosp into corresponding
fields of table gt_cosp
where objnr in r_objnrs
and lednr = lv_lednr
and versn = p_vercost
and wrttp = lv_wrttp
and gjahr in r_gjahr
and kstar in r_kstar.
The suggested solution is to use internal table instead of a range and use "for all entries" or to divide the ranges to smaller units.
As you see I have few ranges and this solution could be not effective in this case.
Do you have a better idea?
Thanks in advance,
Far
09-22-2005 8:47 AM
Welcome to SDN
i don't think ranges can give any runtime error here
regards
09-22-2005 8:54 AM
hi,
can you post the error message and how manay entries you expect from the table
cheers,
sasi
09-22-2005 8:55 AM
Yes ranges have a limitation if number of entries are high. This depends on some database parameter.
If you are not sure of how many entries will be going in the ranges table at runtime, it is prferable to use "for all entries" , the compiler automatically fragments the select statements depending on database settings and you need not check this in ABAP code.
In our system select fails if ranges have more than 3000 entries.
Cheers.
09-22-2005 9:01 AM
sanjay thanks for info
i think we can set this number(3000) for no. of entries
what do you say
regards
09-22-2005 9:04 AM
Hi Fariba
You can tried to limit the number of records are loaded in table range.
So I don't know how you fill those range, but you do it in more steps than one.
For example:
LOOP AT ITAB.
R_RANGE(3) = 'IEQ'.
R_RANGE_LOW = ITAB-FIELD.
APPEND R_RANGE.
ENDLOOP.
SELECT * FROM <TABLE> INTO TABLE MY_TABLE
WHERE FIELD IN R_RANGE.
You could do:
DESCRIBE TABLE ITAB LINES MAX_LINE.
INDEX_FROM = 1.
INDEX_TO = 100.
DO.
LOOP AT ITAB FROM INDEX_FROM
TO INDEX_TO.
R_RANGE(3) = 'IEQ'.
R_RANGE_LOW = ITAB-FIELD.
APPEND R_RANGE.
ENDLOOP.
SELECT * FROM <TABLE> APPENDING INTO TABLE MY_TABLE
WHERE FIELD IN R_RANGE.
INDEX_FROM = INDEX_TO + 1.
IF INDEX_FROM > MAX_LINE.
EXIT.
ENDIF.
INDEX_TO = INDEX_TO + 100.
ENDDO.
In this way you select for all 100 hit.
How to solve your problem depend on how you have to fill the range.
Max
09-22-2005 9:25 AM
Surpreet ,
Not really the way you see . In database you have a limit on the length of SQL statement.
SAY
Select * from MARA where MATNR in S_MATNR.
The databse interpretor will split the above into
Select Field1 Field2 .... from mara
where matnr eq M1
or matnr eq M2
......
......
If you have large number of entries the select statement may reach the thresold value and will fail. Thresold value is a database parameter .
When runtime error occurs you can see this in SM21 system log and see which parameter caused runtime error .
( Other parameter may cause eroor as well ).
In ST05 you can see how select statements are passed to database.
"For all entries" takes care of above problem .
It splits the select statement depending on DB parameters dynamically.
Hope this is helpful.
Cheers
09-22-2005 9:28 AM
Sanjay,
thanks a lot for this info
regards
i did not know this initially
09-22-2005 9:59 AM
Thanks to all of you,
but unfortunately all these answers don't help in my case.
You see, "for all entries" could be helpful if each line in the range contain only one value...
in my case the "low" and "high" are different, and that's the reason I use range value.
(this range is reached by using a function witch split a group to values)
The above solution is too clumsy and complicated, and I was expected SAP has more intelligent solution.
Thanks in any case.
Fariba
09-22-2005 10:02 AM
09-22-2005 10:12 AM
Hi all,
I have had the same problem (with Oracle DB) and, looking for some SAP notes, I found the solution that max bianchi has suggested.
I think it's the only solution.
Regards, Manuel
09-22-2005 1:50 PM
The easiest thing to do in all such cases is to get the records without that range and then delete them as follows.
select * from cosp
into corresponding fields of table gt_cosp
where lednr = lv_lednr
and versn = p_vercost
and wrttp = lv_wrttp
and gjahr in r_gjahr.
if not gt_cosp[] is initial.
delete gt_cosp where not ( kstar in r_kstar and
objnr in r_objnrs ).
endif.
If you think your 'r_gjahr' will also contain lot of records, then you should move that also into the delete statement and take it out of the select statement.
Please don't forget to reward the helpful answers and close the post once resolved.
Regards,
Srinivas
Srinivas