we at one point used alot of this: > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (<@ARRAY request$valuelist APREFIX='' > ASUFFIX='' CPREFIX='' CSUFFIX='' RPREFIX='' RSUFFIX=','>0)
but then as our clients collected more data we found out (atleast for R:Base not sure about others...could be an odbc limitation though) that there is a max of 4000 characters or 500 items in the DBMS query. we had alot of these things explode on us from getting too large and quickly changed to using the subquery format: > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (SELECT othertablecolumn FROM othertable > WHERE somecolumn = '<@VAR request$varname>' ) and now all is well! ----- Original Message ----- From: "Bill Downall" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, September 08, 2003 11:56 AM Subject: Re: Witango-Talk: DBMS action > Ted, > > There is not a performance hit, unless you don't have an index on the > column that generates the list. Your best bet, since you are using a direct > DBMS action, is a sub-query: > > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (SELECT othertablecolumn FROM othertable > WHERE somecolumn = '<@VAR request$varname>' ) > > But, with Oterro/R:Base, be sure NOT to have any line breaks between the > parentheses that surround the subquery. > > On the other hand, if you do not use a subquery, and use something like > this... > > SELECT a, bunch, of, columnnames > FROM maintable > WHERE columnvalue IN (<@ARRAY request$valuelist APREFIX='' > ASUFFIX='' CPREFIX='' CSUFFIX='' RPREFIX='' RSUFFIX=','>0) > > (The "comma-zero" at the end helps deal with the fact that the RSUFFIX > comma character leaves a comma at the right end of the valuelist array, > and you need to put one more dummy value after that last comma.) > > ... then, eventually a user might generate such a long list that you will get a > database error message something like "Expression size limit exceeded." > You will have to have your code test for the size of the list, and, if necessary, > take evasive action. > > Bill > > On Mon, 8 Sep 2003 14:46:10 -0400, Ted Wolfley wrote: > > >I am using a DBMS action to retrieve rows from a Rbase database. In the > SQL > >select code, I am using the IN syntax to retrieve each row's key field that > >matches the list of key fields retrieved from another table. It works with > >5 key fields in the list and I am wondering if there is a limit on the > >number of values that can be in the list? Or if there is performance hit > >the number goes up? > > > > > ________________________________________________________________________ > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
