Re: Stored Procedure bug in Informix 7.3

The SQL optimizer cannot determine whether or not it can use the optimizer because it does not know if the incoming parameter begins with a matching character. For example, if C is '013%', then the index can be used but if it is '%123', the index cannot be used. The access plan has already been built by the time the parameters are sent and so it cannot use the index. MSN wrote: > Hi, > > I have the following code in my stored procedure: > > "where A.lmatter matches ('013169' || '*') " > > This code above runs in less then 2 seconds > > if I replace it with; > > "where A.lmatter matches (C || '*') " > > Where C is a parameter. The program runs for more then 1 minute. Any idea ? > Please help. > > create procedure test113(c char(6), i integer) > RETURNING CHAR(12), Integer, CHAR(20);> > DEFINE matter Char(12); > DEFINE Invoice Integer; > DEFINE BillDate Date; > > FOREACH > Select * > into matter, Invoice, BillDate > from PW_PHMainMTD24 A> > where A.linvoice=1929 > --and A.lmatter matches ('013169' || '*') > and A.lmatter matches c || '*' > > RETURN matter, invoice, matter; > > END FOREACH > > END PROCEDURE; > > execute procedure test113('013169', 1929);