Mike Thanks. I had considered that alternative but I believe there may be a glitch in the syntax editor as I should not have to code two lines since the following works in a select statement: SELECT (SRPL(&FxTXHqColumn,CHAR(44),NULL,0))=40 FROM &FxTXHqTable WHERE &FxTXHqColumn CONTAINS CHAR(44)
Here is the results of executing that statement: (SRPL(TXHqLastName,CHAR(44),NULL,0)) ---------------------------------------- Gutierrez Note! The SRPL statement is exactly the same as in my UPDATE statement. Why does it work in one case and not the other? I have decided to open a problem with RDCC. Jim Bentley, American Celiac Society 1-504-737-3293 ________________________________ From: MikeB <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thursday, March 27, 2014 10:50 PM Subject: [RBASE-L] - Re: Store procedure question James, I got it to work [using my own data] with the following construct: SET VAR vTableName TEXT = 'vehicle' SET VAR vcolname TEXT = 'vehmfr' set var vSrpl = + ('(SRPL(' + .vColName + ',' + '''ford''' + ',' +'''fordTwo''' + ',' +'0))') UPDATE &vTableName SET &vcolname = &vsrpl WHERE &vcolname CONTAINS 'for' RETURN > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of James > Bentley > Sent: Thursday, March 27, 2014 10:16 PM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Store procedure question > > Mike, > Here is what I am attempting to do: > fxtxhqcolumn = TXHqLastName TEXT > FxTXHqTable = TXHqBetaXi TEXT > The table name and column name are passed as parameters to stored > procedures See above values. > > If I execute the following command: > select &FxTXHqColumn FROM &FxTXHqTable WHERE &FxTXHqColumn CONTAINS > CHAR(44) > TXHqLastName > -------------------- > Gutierrez, > Donelon, > Heard, > Orr, > Tolivar, > Spreen, > Palermo, > Jeandron, > Blackledge, > Cradic, > Cangelosi, > Neupert, > Blackman, > Allen, > Pecoraro, > Vilcan, > Whittaker, > Magner, > > We get a list of TXHqLastName that contain a comma. > > If I enter the following command: > select (SRPL(&FxTXHqColumn,CHAR(44),NULL,0))=40 FROM &FxTXHqTable > WHERE &FxTXHqColumn CONTAINS CHAR(44) > (SRPL(TXHqLastName,CHAR(44),NULL,0)) > ---------------------------------------- > Gutierrez > Donelon > Heard > Orr > Tolivar > Spreen > Palermo > Jeandron > Blackledge > Cradic > Cangelosi > Neupert > Blackman > Allen > Pecoraro > Vilcan > Whittaker > Magner > > notice that the comma has been removed. the &FxTXHqColumn is required > to provide the actual column name. > > If I execute the following command I get the indicated results. > > UPDATE &FxTXHqTable SET &FxTXHqColumn = (SRPL( > &FxTXHqColumn,CHAR(46),NULL,0)) WHERE &FxTXHqColumn CONTAINS CHAR(46) > -ERROR- A left parenthesis cannot be followed by a binary operator > (2145) > > what is intended is for RBase to translate the statement into: > UPDATE TXHqBetaXi SET TXHqLastName=(SRPL(TXHqLastName,CHAR(44),NULL,0)) > WHERE TXHqLastName > > CONTAINS CHAR(46) > > > CHAR(46) IS AN ASCII COMMA > > Instead I get the above error message. > > > Jim Bentley, > American Celiac Society > 1-504-737-3293 > > > > ________________________________ > > From: MikeB <[email protected]> > To: RBASE-L Mailing List <[email protected]> > Sent: Thursday, March 27, 2014 5:07 PM > Subject: [RBASE-L] - Re: Store procedure question > > > Why would you use an ampersand var inside of the function? > > In that context the variable is a VALUE and not a part of a command to > be > parsed. > > > -----Original Message----- > > From: [email protected] [mailto:[email protected]] On Behalf Of Bill > > Downall > > Sent: Thursday, March 27, 2014 4:59 PM > > To: RBASE-L Mailing List > > Subject: [RBASE-L] - Re: Store procedure question > > > > Jim, > > > > Since your Macro variable &FxTXHqColumn is enclosed within > parentheses > > and is therefore part of an "expression", you have probably confused > > the parser into not substituting the macro variable soon enough. > > > > Just for giggles, see what happens if you put a space between the > > parenthesis and the ampersand. > > > > I would also put CHAR(46) into parens, because that syntax could also > > stymie a parser. Or better yet, put it in a variable. > > > > SET VAR vDot = (CHAR(46)) > > > > UPDATE &FxTXHqTable SET + > > > > &FxTXHqColumn = (SRPL( &FxTXHqColumn, .vDot, NULL, 0)) + WHERE > > &FxTXHqColumn CONTAINS .vDot) > > > > > > > > If that doesn't help, let us know, and we'll escalate. > > > > Bill > > > > > > On Thu, Mar 27, 2014 at 4:29 PM, James Bentley > > <[email protected]> wrote: > > > > > > I am trying to modify one of my stored procedure to be more > > generalized. > > The following is a part of the code > > SET VAR FxTXHqTable=(.FxTXHqTableName) > > SET VAR FxTXHqColumn=(.FxTXHqColName) > > -- Remove period, comma and double space > > UPDATE &FxTXHqTable SET > > &FxTXHqColumn=(SRPL(&FxTXHqColumn,CHAR(46),NULL,0)) WHERE > &FxTXHqColumn > > CONTAINS CHAR(46) > > UPDATE &FxTXHqTable SET > > &FxTXHqColumn=(SRPL(&FxTXHqColumn,CHAR(44),NULL,0)) WHERE > &FxTXHqColumn > > CONTAINS CHAR(44) > > UPDATE &FxTXHqTable SET &FxTXHqColumn=(SRPL(&FxTXHqColumn,' > > ',CHAR(32),0)) > > > > > > The stored-procedure has two arguments FxTXHqTableName TEXT (18) > > AND FxTXHqColName TEXT (18) > > They represent a tablename and a column name. > > The error message given is that left parenthesis can not be > > followed by '&' > > this part of the syntax "(SRPL(&FxTXHqColumn" seems to be the > > problem > > it works if I substitute the actual table name for example > > "MbrCurHMainAdrLine" which > > is the value I am passing to the stored procedure. note the other > > parts of > > > > the abatement with the "&" worked fine. > > > > The are some 50 lines of UPDATE commands in the procedure. > > > > Does any one have any suggestions as to how I can fix this > > problem. > > > > setting up the complete line and into a variable the esecuting > > the variable seems > > to defeat the purpose of generalizing the stored procedure. > > > > > > Jim Bentley, > > American Celiac Society > > 1-504-737-3293 > > > > > > > > > >
