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



Relevant Pages

  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)
  • RE: Xlocking with a select statement
    ... named query expression, order clause, update clause, lock option ... A result table or the underlying base tables are updateable if the query ... A lock can be requested for the ...
    (microsoft.public.sqlserver.programming)
  • Re: update query: still having problems
    ... "Michel Walsh" wrote: ... From the User Interface, in the toolbar, or the menu, when you edit a query, ... If this is what you want, fine, else, add a WHERE clause to limit ... SELECT Department.*, sheet1.* ...
    (microsoft.public.access.queries)
  • Re: update query: still having problems
    ... "Michel Walsh" wrote: ... you should find a button that allows you to change the query "type". ... If this is what you want, fine, else, add a WHERE clause to ... SELECT Department.*, sheet1.* ...
    (microsoft.public.access.queries)
  • Re: VB-ADO-SQL Server : SQL Server performs logins after some queries
    ... If you have some joins or WHERE clause in your statement, ... Also try to minimize selection of the records using WHERE ... Incase of actual action query, ... >> of queries and I've concluded that in case of an internet conection the ...
    (microsoft.public.vb.database.ado)