Filtering database records 
Author Message
 Filtering database records

A colleague advised me not to use SET FILTER TO because it's not very
efficient.  So I tried to use a different method for filtering my data.
However, it doesn't seem to work.  Can you tell me why it's not working,
or recommend a better way for doing this:

I am trying to filter a database for a report by copying data that fits
the criteria into a new database, and passing this database to the
report.  This code works:

SELECT logbook
SET FILTER TO ((logbook.loginTime >= dStartDate) .AND.
(logbook.loginTime <= dEndDate) )
SCAN
   COPY TO repTable
ENDSCAN
SET FILTER TO

However, this does NOT work (It copies everything):

SELECT repTable
APPEND FROM logbook FOR ( (logbook.loginTime >= dStartDate) .AND.
(logbook.loginTime <= dEndDate) )

Also, I tried this, and it does NOT work either (It copies the whole
table):

SCAN FOR BETWEEN(logbook.loginTime, dStartDate, dEndDate)
   COPY TO repTable
ENDSCAN

Thanks for your help.

- Mon



Sun, 27 Aug 2000 03:00:00 GMT  
 Filtering database records


Quote:
> SELECT logbook
> SET FILTER TO ((logbook.loginTime >= dStartDate) .AND.
> (logbook.loginTime <= dEndDate) )
> SCAN
>    COPY TO repTable
> ENDSCAN
> SET FILTER TO

You don't need to embed COPY TO in a SCAN.. although perhaps it wouldn't
really hurt anything because your COPY TO moves the record pointer to the
end and ends the scan loop.  I'm personally against doing anything inside a
SCAN loop that moves the record pointer.  See the help on COPY TO... it has
FOR clauses that you can use to accomplish everything you are trying to do
here.  And it's Rushmore optimizable.

Quote:
> SELECT repTable
> APPEND FROM logbook FOR ( (logbook.loginTime >= dStartDate) .AND.
> (logbook.loginTime <= dEndDate) )

Dunno.  I have rarely used APPEND FROM.

Quote:
> SCAN FOR BETWEEN(logbook.loginTime, dStartDate, dEndDate)
>    COPY TO repTable
> ENDSCAN

No, this indeed would not work.  Your SCAN may filter the records that you
visit, but the COPY TO just copies everything anyways.  AND it moves the
record pointer so your SCAN is useless.  See the help on COPY TO and pay
attention to the Scope (FOR) clauses in particular.


Sun, 27 Aug 2000 03:00:00 GMT  
 Filtering database records

You could use a SQL Select if adding records to an existing table.
Otherwise, as mentioned, use the COPY TO...FOR command

Select * from whatever into cursor temp where [date range] order by whatever

cursor_name = dbf()
select reptable
append from &cursor_name

Quote:

> A colleague advised me not to use SET FILTER TO because it's not very
> efficient.  So I tried to use a different method for filtering my data.
> However, it doesn't seem to work.  Can you tell me why it's not working,
> or recommend a better way for doing this:



Sun, 27 Aug 2000 03:00:00 GMT  
 Filtering database records

Quote:

> A colleague advised me not to use SET FILTER TO because it's not very
> efficient.  So I tried to use a different method for filtering my data.
> However, it doesn't seem to work.  Can you tell me why it's not working,
> or recommend a better way for doing this:

> I am trying to filter a database for a report by copying data that fits
> the criteria into a new database, and passing this database to the
> report.  This code works:

> SELECT logbook
> SET FILTER TO ((logbook.loginTime >= dStartDate) .AND.
> (logbook.loginTime <= dEndDate) )
> SCAN
>    COPY TO repTable
> ENDSCAN
> SET FILTER TO

> However, this does NOT work (It copies everything):

> SELECT repTable
> APPEND FROM logbook FOR ( (logbook.loginTime >= dStartDate) .AND.
> (logbook.loginTime <= dEndDate) )

> Also, I tried this, and it does NOT work either (It copies the whole
> table):

> SCAN FOR BETWEEN(logbook.loginTime, dStartDate, dEndDate)
>    COPY TO repTable
> ENDSCAN

> Thanks for your help.

> - Mon

  I think what your colleague had in mind was to use SELECT SQL and to base
the report on the result.

SELECT * FROM logbook ;
WHERE ogbook.loginTime >= dStartDate) .AND. ;
logbook.loginTime <= dEndDate ;
INTO CURSOR temp ;
ORDER BY < whatever order you need>



Sun, 27 Aug 2000 03:00:00 GMT  
 Filtering database records

Thanks for the tip Brian.  I haven't used SQL SELECT in my programs
before.  Is it more efficient than using something like COPY TO
<newfile> FOR <condition>?  Do I need to make the logbook.loginTime an
index for this to work in my program?
Thanks.

                  \\\|///
                \\  - -  //

+--------------oOOo-(_)-oOOo--------------+
|               Mon Nasser                |
|         http://www.ao.net/~mon          |
+-----------------------------------------+

Quote:

>   I think what your colleague had in mind was to use SELECT SQL and to base
> the report on the result.

> SELECT * FROM logbook ;
> WHERE ogbook.loginTime >= dStartDate) .AND. ;
> logbook.loginTime <= dEndDate ;
> INTO CURSOR temp ;
> ORDER BY < whatever order you need>



Mon, 28 Aug 2000 03:00:00 GMT  
 Filtering database records

Any index you can create on a field you use for Scoping or Comparison
(including SQL Select joins) will help your cause(speed things up), if you
do it right.  Experimentation was about the only thing that helped me
figure out how to use indexes to my advantage.  Keep in mind that it's a
good idea to be judicious though.. More indexes on a table means more
indexes to update when you add new records or modify, or delete...  That
can slow performance if you've got way too many.

That's what I've always heard, anyways.  I have good proof of my first
assertion, the second is only hearsay to me but it makes sense.


Quote:
> Thanks for the tip Brian.  I haven't used SQL SELECT in my programs
> before.  Is it more efficient than using something like COPY TO
> <newfile> FOR <condition>?  Do I need to make the logbook.loginTime an
> index for this to work in my program?
> Thanks.



Mon, 28 Aug 2000 03:00:00 GMT  
 Filtering database records

A few years back I was adding about 50K records to a table.  This would be
done on a monthly basis.  "Watching grass grow" was as much fun as the append
with indexes.  The process was dramatically speeded up by deleteing tag all,
appending, then rebuilding.  Of course, this would be done when everyone is
off the system but adding 50K records in batch is a fairly large update.  I
wouldn't be too concerned when adding single records to a table via DE.

Quote:

> figure out how to use indexes to my advantage.  Keep in mind that it's a
> good idea to be judicious though.. More indexes on a table means more
> indexes to update when you add new records or modify, or delete...  That
> can slow performance if you've got way too many.



Mon, 28 Aug 2000 03:00:00 GMT  
 Filtering database records

Quote:

> Thanks for the tip Brian.  I haven't used SQL SELECT in my programs
> before.  Is it more efficient than using something like COPY TO
> <newfile> FOR <condition>?  Do I need to make the logbook.loginTime an
> index for this to work in my program?
> Thanks.

Copy to is faster if you have only 1 table involved.  SQL shines in
multi table pulls.  Making an index on the field that is in the filter
is always a good way to speed up copy to or sql select.


Mon, 28 Aug 2000 03:00:00 GMT  
 Filtering database records

Common sense, but I never thought of doing this before.  I like it.  Thanks
=)
I guess I haven't had to do mass appending in a while, so maybe that's why
it hasn't been a major issue to me.



Quote:
> The process was dramatically speeded up by deleteing tag all,
> appending, then rebuilding.



Tue, 29 Aug 2000 03:00:00 GMT  
 Filtering database records

Furthermore, what you are saying sounds a lot similar to me to what SQL
Server does when you use BCP.



Quote:


> > The process was dramatically speeded up by deleteing tag all,
> > appending, then rebuilding.



Wed, 30 Aug 2000 03:00:00 GMT  
 Filtering database records

Mon,
  Try looking at the SQL SELECT command, to select a subgroup of your data
table to a cursor and use that in your report..


Quote:
> A colleague advised me not to use SET FILTER TO because it's not very
> efficient.  So I tried to use a different method for filtering my data.
> However, it doesn't seem to work.  Can you tell me why it's not working,
> or recommend a better way for doing this:

> I am trying to filter a database for a report by copying data that fits
> the criteria into a new database, and passing this database to the
> report.  This code works:

> SELECT logbook
> SET FILTER TO ((logbook.loginTime >= dStartDate) .AND.
> (logbook.loginTime <= dEndDate) )
> SCAN
>    COPY TO repTable
> ENDSCAN
> SET FILTER TO

> However, this does NOT work (It copies everything):

> SELECT repTable
> APPEND FROM logbook FOR ( (logbook.loginTime >= dStartDate) .AND.
> (logbook.loginTime <= dEndDate) )

> Also, I tried this, and it does NOT work either (It copies the whole
> table):

> SCAN FOR BETWEEN(logbook.loginTime, dStartDate, dEndDate)
>    COPY TO repTable
> ENDSCAN

> Thanks for your help.

> - Mon



Fri, 01 Sep 2000 03:00:00 GMT  
 
 [ 12 post ] 

 Relevant Pages 

1. Filtering records / Manipulating multiple database records...

2. Status Bar - Silencing the Current Database, Record No/No of Records etc

3. Record 1 of 6 (Filtered)

4. Grid slower than browse screen on filtered records

5. Filtering Records

6. Problem printing filtered record(s).

7. Print Filtered Record(s).

8. # records in filtered table

9. results: Filtering no-match record/field (SELECT-SQL)

10. Problem printing filtered record(s).

11. Filtering records

12. Filter Cleared at runtime, when one record found

 

 
Powered by phpBB® Forum Software