DELETE AND APPEND QUERIES 2

[cobweb]

Hi there:

I am new to VFP - VFP 8 in this instance - and am looking for a delete query and append query feature in the query designer...but cannot.
Do these queries have to be written in SQL or does the designer allow me to do this?

Thanks!

 

[Olaf Doschke]

Hi cobweb!

Do you mean the view designer?

The view designer is there to define views, and views are not only capable of selecting, but if you define them as updatable, they will also update tables and delete records if needed.

Bye, Olaf.

 

[alvechurchdata]

The Query Designer doesn't do a Delete Query either. You can write them in SQL or use the FoxPro DELETE command - something like:

DELETE ALL FOR country = "UK"

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[cobweb]

Thanks to you both for this advice: I am sorry it was what I call a 'lazy' question - the proper answer should have been RTFM!
I want to use VFP to replace Paradox as a Data Warehouse, pulling large amounts of data from an Oracle database. I wrote an 'incremental' append query in Paradox where the Query would find the last record in the Warehouse then append only those records in Oracle that had been entered since the last append. My question should have been...is this possible in VFP?
And I am looking at Amazon now for a good VFP manual..any suggestions?

Thanks again

 

[alvechurchdata]

How about a sub query to insert the records which don't already exist in the warehouse?

insert into warehousetable ;
  select (fields) from oracletable ;
  where pk not in ;
    (select pk from warehousetable)

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[cobweb]

Thank you very much, Geoff.
Does this, however, mean I am misunderstanding SQL?
THe Oracle table has over 750000 records and I thought that an incremental append would speed things up, by only selecting those after a certain number. The subquery will trawl through all 750000 records, I presume...but does the incremental append do that also and I am simply wrong to think otherwise?
In other words will the subquery be just as fast as an incrementally rolling query?
Thanks!

 

[Olaf Doschke]

Hi cobweb,


if you stored the last pk somewhere, then a select like
select * from oracletable where pk>lastpk would be much faster.

But is it really that simple? How about records that were updated in oracle? Or deleted? Keeping in sync may not be that easy.

Bye, Olaf.

 

[cobweb]

Olaf:

Thanks for that.
You are right. They are sales orders that come in; and every month I reconcile these to the accounts database by deleting and reappending all records for the month just ended. I warned you I was a novice!
In fact the main reason I am looking at VFP is Rushmore; I am hoping to read Oracle views directly instead of downloading to a warehouse. Then I can discard the Warehouse and create queries/reports etc based on the views and local VFP tables. I had done this in Paradox a few years ago but Oracle became sluggish with so many users - maybe VFP (and a new linux server)will speed things up.
To get me started though I am trying to simply emulate Paradox and run time trials.So stage 1 is repeat paradox, stage 2 is move forward. This is stage 1.

Thanks to you all.

 

[TamarGranor]

To answer your question about books, check out

http://www.hentzenwerke.com.

For a "how to write VFP applications," check "The Fundamentals" by Whil Hentzen. If you're looking for a desk reference, check out "Hacker's Guide to VFP 7" (disclaimer: I'm one of the authors). For a "how do I do this" volume, look at "1001 Things ..."

Tamar