topic select problem in Application Development and Automation Discussions

select problem

Former Member — Thu, 18 Sep 2008 09:31:26 GMT

Hi,

i do this select with while loop.

the logic is that the select is running until i get in ukurs data ,

otherwise it add 1 to datecur and and try to find ukurs.

the problem is that in prod it run to long there is a way to improve this performance?

Regards

ukurs = ''.

WHILE ukurs = ''.

SELECT SINGLE ukurs

FROM tcurr

INTO ukurs

WHERE kurst = '1002' AND

fcurr = wa_dp-currency AND

tcurr = 'USD0' AND

gdatu = datecur.

datecur = datecur + 1.

ENDWHILE.

Re: select problem

Former Member — Thu, 18 Sep 2008 09:37:25 GMT

It would probably be best to SELECT all kurst = '1002' entries into an internal table and then loop around that. This is preferable to doing many SELECT statements within a loop.

However how many entries are in your TCURR table? The current system I work on only has 200 so I can't equate it to a performance issue if I were to run your code.

Re: select problem

Former Member — Thu, 18 Sep 2008 09:39:35 GMT

Hi,

If you write the SELECT Query inside Loop it will take time to execute. And performance will decrease.

So if possible Avoid Select inside Loop

Re: select problem

Former Member — Thu, 18 Sep 2008 09:43:40 GMT

Also reconsider the checking of ukurs = '' since it is not a character-based field. Your loop may not actually be exiting

Re: select problem

JozsefSzikszai — Thu, 18 Sep 2008 09:59:54 GMT

hi Ricardo,

if I understand right you need the exchange rate for a given date (datecur) or closest to that date in the future. You can replace the WHILE-ENDWHILE with the following SELECT:

SELECT ukurs MIN( gdatu )
INTO ...
FROM tcurr
WHERE ... gdatu GE datecur
GROUP BY ukurs gdatu.

The trick is to use SELECT ... MIN( gdatu ) which will only select the possible lowest date (pls. note the comparison in the WHERE: gdatu GE datecur

Of course this is not complete, you have to add the other conditions as well.

hope this helps

Re: select problem

Former Member — Thu, 18 Sep 2008 10:52:09 GMT

Hi Eric,

Thanks u understand right ,

but how your select is now if it dont find value,

go to next day and so on?

Regards

Re: select problem

JozsefSzikszai — Thu, 18 Sep 2008 11:15:41 GMT

sorry, what i wrote aboe is not correct, it still selects all lines. I think you have to go for array select:

SELECT gdatu ukurs
FROM tcurr
INTO TABLE ...
WHERE ... gdatu GE decdate

this will select all possible entries in one go

SORT itab BY gdatu.
READ TABLE itab INTO ... INDEX 1.

internal table will be sorted with date, which means, the first line is what you'll finally need.

hope this helps (finally)