What is the length limitation on a SELECT statement, e.g.

SELECT fname WITH field 'x' 'x' 'x' 'x'…………… by field

I have a client who is trying to select a list of criteria (warehouse locations) from a list of indexes and he gets no error message, he just gets kicked out to DOS.

Any input would be appreciated.

There is a limit, which is I think around 256 characters, I haven't ever figured this out to a precise count. It just sometimes too small and you have to "do the dance".

To avoid stepping into the limit, you could make a symbolic that returns true/false if the record has one of the desired locations. But then if the set of locations changes, you'll be updating this symbolic often.

Or you might be able to implement some new code, that is a 'district' or some other grouping of warehouses (finished goods, old style/new style, leased or owned, etc) and in the locations ("fname") table assign each warehouse location a district_code.

Then your select would be:

 select fname with district_code=1 2 3 7 99 

Another possibility is the location codes might lend themselves to a pattern match

select fname with field starting "A" "G4" "X" "123" 

   or some pattern

You can combine selects (back-to-back) in a multiline tcl statement too to further reduce the data. We often do this to avoid those long selects, and in particular when the first select can be on an indexed field. The back-to-back solution I don't think would lend itself to your problem, as it can't add rows back into the record set, just reduce them further.

The limit is more than 256. I just did a test on version 2.12 the worked with 100 items in a lest (10 digits each quoted, so well over 1000 characters). The session hung, however, when I attempted 1000.

Somewhat related, @SENTENCE seems to be limited only by maximum record size of the TCL window. Try running the program:

CALL MSG(LEN(@SENTENCE))

calling it with arbitrarily long arguments.

It's interesting, the size limit on a SELECT sentence. Up until 3.12, it used to be about 1000. At 3.12 it changed to around 6000. As far as I know, OI was increased at the same time, so that would have been around OI 2.01 or 2.5.

The problem with sizes though is that it's not really a strict limit on the size, it's a limit on the size and structure of the sentence and the resultant output. This is the same for R/LIST and OpenList sentences and R/BASIC and Basic+ programs. Both goes through the meta logic, which parses the sentence and turns it into something the processing engine (AREV.EXE or OENGINE.DLL) can understand or process.

Where it gets complicated is that these programs are both complicated, arcane, but most importantly for the subject at hand, very tight on memory. To save space, there's some shared memory space used. The idea is the the resultant output will be smaller than the source input, so the parser eats its own tail. That means that as the input is processed, it's space is cleared out and replaced by the output. This process can occasionally lead into situations were the output overwrites the input. This, obviously, corrupts the sentence.

The question now is how to resolve this, and there's not that many things you can do. As Ralph suggests, you can combine some clauses into a single clause. The usually clears the problem. Alternatively, the problem is sometimes resolved by reordering the sentence clauses. This means instead of

SELECT FILE WITH FIELD1=VALUE' AND FIELD2=OTHER VALUE'

try

SELECT FILE WITH FIELD2=OTHER VALUE' AND FIELD1=VALUE'

The Sprezzatura Group

World leaders in all things RevSoft

Revelation Conference 2007, London - Wednesday 27th June Click here to register for the premier Revelation Software EMEA event of 2007

Sometimes I run a SELECT where

perform 'SELECT file WITH var=x,x,x,x,x,x,x,x,x,x,x

Each x Value has "" around it and the list is so long that

the program crashes, again this is due to command length.

Easy and good work around for these problems is a looping routine

in a program where you select a list based on an indexed field

then process a certain number of this list at a time.

Then send the records selected to a gosub routine that continues

the selection process till you get the resaults your looking for.

Of course Variables are limited to 64K so if the original select is

too large you can use dimentioned arrays and loop thru them.

This is just a quick how to, if you would like an example I can email you something to look at.

My email - [email protected]

If the resulting select list could be over 64K, you could just build it in LISTS instead, then when done do a getlist then the report. A few of our specialized selects do just this, writing a new portion to the "savelist" as each chunk hits 32K.

A limit of 1000 characters! Thanks I wasn't aware of that.

I always thought it was much smaller. Of course this proves my selects are actually much more *powerful* than average, since I can crash a select quite quickly…

A test I did last week worked fine up to about 1730 characters (145 record keys). At 145 keys, the Arev session aborted. At over 145 keys, it worked, but one record was missing. In other words, a select with 148 keys itemized would return a select list with 147 keys. I did not check to see which one was missing.

for up to 64k of keys, Make.List is useful too.

The length also depends on the internal representation of the values being selected on – pure numbers can have a longer string (up to the variable limit, of course) than strings.

It's an annoyance of AREV that most of us have run into at least once.

True.

But wouldn't you know that when we ran into trouble with the select statement length (or wanted to select one file based on records in another), we also ran into the 64K limit in real customer data. So those general utilities/selects just assumed the return list would always be over 64K and just broke it into chunks. That way it always works, rather than just most of the time.

Thanks to all who responded….the variety of responses just goes to strengthen my observations that in the wonderful world of AREV, there's always more than one way to skin a cat - some more painful than others :)

I will try all suggestions and hopefully post my conclusions…if I reach any.

Thanks ALL.

View this thread on the forum...