Infininate loop problem? going beyong 254 values for in function

[FranckM]

Guys, I'm trying to create a statement that will allow me to have more then 254 values in an in statement. I'm trying to do this with the following statment. When I try to use it I get a scripttimeout error, even if I increase the timeout. I'm baffaled, I don't know what the problem could be. Any help would be aprreciated.

Thanks for taking the time to look at my code.


'88888888
dim arrWords, iLen, icounter, counter, inValues
const divider = 254
arrWords = split(strInstruments,",&quot
iLen = UBound(arrWords)
iLen = iLen / divider

icounter = ubound(arrwords)
counter = 0
stop
do while icounter <> 0
if counter <= 254 then
inValues = inValues & arrwords(counter + counterMod) & &quot;,&quot;
else
strSQL = strSQL & &quot; AND band_reserve.reserve_number IN ('&quot; & inValues & &quot;')&quot;
if counter = 255 then
tempSql = tempSql & strSQL & &quot; union &quot;
inValues = arrwords(counter + counterMod)

counterMod = counter + counterMod
counter = 1

else
tempSql = tempSql & strSQL
icounter = 0
end if
counter = 1 + counter
end if
loop
strSQL = tempSql
'88888888

 

[Tarwn]

Two problems:
1) you are incrementing your counter only when it is greater than 254, so your loop runs forever adding word the word from counter + counterMod (in this case 0 + counterMod)
I don't see an assignment for counterMod before your loop so if we assume it is 0 before the loop starts you will be essentially writing an infinite string composed of the first value of the array repeated.
2) If you move the increment of counter to after the end if, you will never reach your nested else statement. Basically at 254 it will enter the first if.
It will add the word to the string.
counter will increment to 255
Code will enter 1st else
counter is 255 so it will enter nested if stmt
counter is reset to 1
loop continues, counter eventually reaches 254 again.

The counter will never reach 256 (the minimum value to pass the combination of not <= 254 and not = 255) because it is reset at 255. Therefore iCounter will never be set to 0 and you have another infinite loop.

Those are the two I saw, I'm not entirely sure what your doing but those look like the only logic errors.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
With enough resources, time, and coffee, anything is possible.

 

[FranckM]

Thank you very much, I'll start fixing those problems ASAP.

 

[FranckM]

I tryed to fix some of the problems. This is what I came up with. I still get the same problem though.

dim arrWords, iLen, icounter, counter, inValues
arrWords = split(strInstruments,&quot;,&quot

do while icounter <= ubound(arrwords)
invalues = invalues & arrwords(icounter) & &quot;,&quot;
icounter = icounter + 1
if counter = 254 then
tempSql = tempSql & strSQL & &quot; AND band_reserve.reserve_number IN ('&quot; & invalues & &quot;') union &quot;
invalues = &quot;&quot;
elseif icounter = ubound(arrwords) then
tempSql = tempSql & strSQL & &quot; AND band_reserve.reserve_number IN ('&quot; & invalues & &quot;')&quot;
else
counter = counter + 1
end if
loop


Thanks for taking the time to look at this.

Frank

 

[GaryC123]

If im following your logic, then i think this is what u need

for icounter =1 to ubound(arrwords)
invalues = invalues & arrwords(icounter) & &quot;,&quot;
if icounter = 254 then
tempSql = tempSql & strSQL & &quot; AND band_reserve.reserve_number IN ('&quot; & invalues & &quot;') union &quot;
invalues = &quot;&quot;
elseif
tempSql = tempSql & strSQL & &quot; AND band_reserve.reserve_number IN ('&quot; & invalues & &quot;')&quot;
end if
Next Saturday 12.00

20.00

3.00am

Sunday [img http

 

[FranckM]

Here's what I want to do.

I want to use the select statment with the in function.

All the values I want to use in the in function are stored in the arrwords array.

I use invalues to store the first 254 values and add them to the select statment.

I empty invalues.

Start the whole process over.

When I I don't have anymore values in the array I add the invalues to an sql statment with no union.

 

[GaryC123]

Sorry but i dont follow what you are trying to do, why are you creating a select statement with 254 values and then clearing it to start again, to end up joining them all together at the end. If I get you right you have arrwords() containing all the individual parts of strInstruments.
Then you want to add each item in the array into a select statement as such -
strSQL = strSQL & &quot; AND band_reserve.reserve_number IN ('&quot; & arrwords()& &quot;')&quot;
Saturday 12.00

20.00

3.00am

Sunday [img http

 

[Tarwn]

Ok, slight modification of gary's code ought to do the trick:

'Loop from 0th element to UBound
for icounter = 0 to ubound(arrwords)
	'Same as gary's: Add the word
	invalues = invalues & arrwords(icounter) & ","
	'If this is 254th value (0 to 253) in a set
	if icounter mod 254 = 253 then
		'Same as gary's: do the sql
		tempSql = tempSql & strSQL & " AND band_reserve.reserve_number IN ('" & invalues & "') union "
		'Clear the invalues
		invalues = ""
	end if
Next
'If the last set had less than 254 entries, we need to add in the remaining invalues
if icounter mod 254 < 253 then tempSql = tempSql & strSQL & &quot; AND band_reserve.reserve_number IN ('&quot; & invalues & &quot;')&quot;

The only problem I see now with the above code is if you have a number of values exactly a multiple of 254. That would cause a hanging union because the last if stmt to add the final values would be skipped.

hope that helps,
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
With enough resources, time, and coffee, anything is possible.

 

[FranckM]

ORA-01795: maximum number of expressions in a list is 254

Is the error that keeps haunting me with either the code above or what I wrote:

do while icounter <= ubound(arrwords)
invalues = invalues & arrwords(icounter) & &quot;,&quot;
icounter = icounter + 1
if icounter = ubound(arrwords) then
tempSql = tempSql & strSQL & &quot; AND band_reserve.reserve_number IN ('&quot; & invalues & &quot;')&quot;
elseif counter = 253 then
tempSql = tempSql & strSQL & &quot; AND band_reserve.reserve_number IN ('&quot; & invalues & &quot;') union &quot;
invalues = &quot;&quot;
counter = 0
end if
counter = counter + 1
loop