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
> >
> >
> >
>
>
>
>
