Re: sql problem

From: Anders Altberg (x_pragma_at_telia.com)
Date: 10/06/04


Date: Wed, 6 Oct 2004 03:08:34 +0200

Vijay
You should definitely not have a reference in a query to any table that
isn't included in the FROM clause of the query. Contactbr is not in the FROM
clause. Turn Contactavdt.orignavdt in to a variable and use the variable in
the query. It's also a good idea to preface variable with M., like M.mdays.
For VFP's Rushmore optimazation to work it's necessary for the column name
to be on the left side of the = equals sign. This [mdays>monthfr] is not
optimized even if monthfr is indexed; you have to change it to
monthfr<M.mdays AND monthto >=M.mdays, or to make it clear WHERE (M.mdays+1
BETWEEN monthfr AND monthto ). given that monfr is less than monthto.
Similaraly: (M.orgavdt BETWEEN slebefffn AND slebeffto ) would be
optimizable if the to columns are indexed.
-Anders

"Vijay V. Nayak" <Vijay Nayak@discussions.microsoft.com> wrote in message
news:556CF9CD-C5C7-437C-874E-05DA1DDB93FE@microsoft.com...
> Respected Rick
> Yes, it's a SQL select on native VFP table. Regarding using service pack
5
> for VFP 6.0, I am not clear how to know that. But I am mentioning my
query
> below for a clear understanding:
> SELE * from slbACBR ;
> where ( Mdays > monthfr and Mdays <= monthto) ;
> and ( rslacs > slbfrom and rslacs <= slbto ) ;
> and ( CONTACBR.ORIGNAVDT >= SLBEFFFM and;
> ( CONTACBR.ORIGNAVDT<=slbeffto OR EMPTY(slbeffto))) ;
> and slb1yrrt > 0 ;
> and allt( FUT_SCH) == 'ALL' ;
> and fundcd = _fundcd ;
> into curs slbcurs1
>
> slbacbr is the ALIAS for table slbmst
> contacbr is the name of a table.
>
> The following are the coloumns of slbacbr which are used in the query
> monthfr, monthto, slbfrom, slbto, slbefffm, slbeffto,
> slb1yrrt, fundcd, fut_sch.
>
> Mdays, rslacs are program variable.
>
> I hope this will clear the counter question from you. Tips are requested.
> Thanks.
>
> "Rick Bean" wrote:
>
> > Vijay,
> > When you say "query", is this simply an SQL select on native VFP tables?
Is it a select on Views, whether local or remote? Are you using service pack
5 for VFP 6.0?
> >
> > Rick
> >
> > "Vijay V. Nayak" <VijayVNayak@discussions.microsoft.com> wrote in
message news:E1D5195B-EF0D-4950-A6C5-897CFD9926CC@microsoft.com...
> > > Hello !
> > >
> > > During program execution when a query is about to be executed, I get
an
> > > error which is 'File is in use'. This is not always. What is the
reason for
> > > this ? How to solve it ?
> > >
> > > The version of VFP is 6.0
> > >
> > > Tips are requested. Thanks,
> > >
> > > Vijay Nayak.
> >