RE: [development-axapta] Filtering on a memo field

[Don Price]

Thanks to everyone who replied.  I did get it working by looping thru the recordset building a container and then using that container to assign to a querybuildRange.  Preston I forgot about the connection/execute to go directly to SQL that should be better performance-wise then my method, so I’ll probably update my code to reflect. Jacob, you brought up something I thought about, but didn’t know the limit on.   So I’ll build multiple ranges on the recid.    Again thanks for everyone’s response, Don   -----Original Message----- From: Jacob Hjelmer Nielsen [mailto:[EMAIL PROTECTED] Sent: Friday, January 23, 2004 1:13 AM To: [EMAIL PROTECTED] Subject: Re: [development-axapta] Re: Filtering on a memo field   Bear in mind that the length of queryranges is not unlimited!.And will cause a runtime Sql-error if it goes beyond approx. 255. Hence You should either decompose the queryrangevaluestring into substrings of 255 and insert info a new range. Or accumulate them i a set, and afterwards use the following metod.:   query ConvertSet2query()         {         QueryBuildRange         QueryBuildRange;         str                             QueryString;         QueryBuildDataSource    QueryBuildDataSource    = query.addDataSource(this.tableid());         SetIterator             It                      = new SetIterator(this.set());         ;             QueryBuildRange     = QueryBuildDataSource.AddRange(this.fieldid());                      if (this.set().empty())             {                 Querystring = Sysquery::valuenotemptystring;             }             else             {                 while (it.more())                 {                     Querystring += it.value()+',';                     if(strlen(Querystring) > 240) // then we are safe                     {                         QueryBuildRange = QueryBuildDataSource.AddRange(this.fieldid());                         QueryBuildRange.value(QueryString);                         QueryString = '';                     }                     it.next();                 }             }             QueryBuildRange     = QueryBuildDataSource.AddRange(this.fieldid());             QueryBuildRange.value(QueryString);         return  query;         } BTW I do not think it is not nessecary to resrtict the search on dataareaid.   :-J Jacob ----- Original Message ----- From: Preston A. Larimer To: [EMAIL PROTECTED] Sent: Friday, January 23, 2004 1:23 AM Subject: RE: [development-axapta] Re: Filtering on a memo field   I forgot to keep in mind the dataAreaID, I figure you’d catch it Don, but incase anyone else is looking.    BTW you’ll notice searching memo fields in the where clause is not a SQL shortcoming, but an Axapta one.   -Preston   static void pl_scratch(Args _args) {     docuRef                         dr;     Connection                      con = new Connection();     Statement                       sqlStmt = Con.createStatement();     ResultSet                       sqlTable;     notes                           sqlStr;     str                             searchWord;     str                             rangeList;     str                             dataareaID;     ;       dataareaID = curExt();       searchWord = "Remark";         sqlSTR = strFmt("select recID from docuref where notes like '\%%1\%' AND refCompanyID = '%2'", searchWord, dataareaID);       info(sqlStr);       sqlTable = sqlStmt.executeQuery(sqlSTR);        while (sqlTable.next()){         //build recidList to use in other query       rangeList = rangeList + (rangeList ? ", " + int2STR(sqlTable.getInt(1)) : int2STR(sqlTable.getInt(1)));           }     info(rangeList);   }   Yahoo! Groups Links ·   To visit your group on the web, go to: http://groups.yahoo.com/group/development-axapta/   ·   To unsubscribe from this group, send an email to: [EMAIL PROTECTED]   ·   Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.   Yahoo! Groups Links ·         To visit your group on the web, go to: http://groups.yahoo.com/group/development-axapta/   ·         To unsubscribe from this group, send an email to: [EMAIL PROTECTED]   ·         Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. Yahoo! Groups Links To visit your group on the web, go to: http://groups.yahoo.com/group/development-axapta/   To unsubscribe from this group, send an email to: [EMAIL PROTECTED]   Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.