Re: Stored Procedures, Visual Basic 6 and LIKE clause

It makes sense, but errors:

[SQL0402] + use not valid. Cause . . . . . :   An operand has been
specified for the arithmetic function or operator + that is not valid.
-- User-defined types cannot be specified as operands of operators or
scalar functions.

On 6/9/05, Wilt, Charles <CWilt@xxxxxxxxxxxx> wrote:
> Remove the quotes.
> 
>         BEGIN
>         declare tempvar varchar(50);
>         set tempvar = '%' + criteria + '%';
>         DECLARE C1 CURSOR WITH RETURN FOR SELECT MAX ( BOX ) AS RETURNED FROM
> CLSDFILES . CLOSEDFILES WHERE BOX LIKE tempvar ; OPEN C1 ;
>         END  ;
> 
> HTH,
> 
> Charles Wilt
> --
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
> 
> 
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Adam Lang
> > Sent: Thursday, June 09, 2005 1:26 PM
> > To: Midrange Systems Technical Discussion
> > Subject: Re: Stored Procedures, Visual Basic 6 and LIKE clause
> >
> >
> > Ok, I think I understand what you are saying, but this leads to a dumb
> > question.  How do I do it?
> >
> > I have been reading through the stored procedures Redbook and can not
> > seem to figure out how to go about this.
> >
> > Thsi si what I came up with, but it won't generate.
> >
> >       BEGIN
> >       declare tempvar varchar(50);
> >       set tempvar = criteria;
> >       DECLARE C1 CURSOR WITH RETURN FOR SELECT MAX ( BOX ) AS
> > RETURNED FROM
> > CLSDFILES . CLOSEDFILES WHERE BOX LIKE '%tempvar%' ; OPEN C1 ;
> >       END  ;
> >
> > It actually errors on the declare C1 cursor part.  I assume it is
> > because the declares have to be together, btu then I wouldn't know how
> > to set the variable after the declare of the statement.
> >
> > On 6/7/05, Walden H. Leverich <WaldenL@xxxxxxxxxxxxxxx> wrote:
> > > Add a row to your dataset with a value of 'check criteria
> > match' for the
> > > box field and try it again, I'll bet you get a row back.
> > >
> > > I'd say the problem is that your LIKE clause is looking for
> > something
> > > that contains the value 'CRITERIA' somewhere in it, that is
> > the letters
> > > C-R-I-T-E-R-I-A and not the value of the variable called CRITERIA.
> > > You'll need to do the concat into a temp variable and then loose the
> > > single quotes. Right now SQL doesn't see that usage of CRITERIA as a
> > > parm, it sees it as a constant.
> > >
> > > -Walden
> > >
> > > ------------
> > > Walden H Leverich III
> > > Tech Software
> > > (516) 627-3800 x11
> > > WaldenL@xxxxxxxxxxxxxxx
> > > http://www.TechSoftInc.com
> > >
> > > Quiquid latine dictum sit altum viditur.
> > > (Whatever is said in Latin seems profound.)
> > >
> > >
> > > --
> > > This is the Midrange Systems Technical Discussion
> > (MIDRANGE-L) mailing list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> > >
> > >
> >
> >
> > --
> > "Your Majesty has slain the mother; how can one kill the
> > child as well?"
> > - Ts'ao Jui, Romance of the Three Kingdoms
> >
> > --
> > This is the Midrange Systems Technical Discussion
> > (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> >
> 
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>