Legacy report (and file) problems

[virgo]

I need to be able to pull information out of a large database (FPW 2.6, 4 different files) and place it very accurately in a fifth file.

This fifth file is then used to run Crystal Reports.

I can get data out of the large database. I cannot get it to go accurately into the fifth file.

I would appreciate any help.

 

[repete]

where exactly is your problem, as you state that you can get the data out of the 4 files OK ?

are you using a sql select statement to get the data into the 5th file ?



Pete Bloomfield
Down Under

 

[virgo]

A little bit more on what the problem is. I separate the 4 files as to whether they are members or subscribers. I also separate both members and subscribers as to whether they are new (no accounting records for this last year) or whether they are renewed (everyone else).

I then run a separate set of those that came through an agency.

I then try and put the results of all of the above into the fifth file which feeds into the Crystal report.

I am using SQL select statements through all of these. However, when I go into the fifth file, then I need to be able to put the data into specific rows and columns in an already existing database.

It does not always end up in the right place when I put the data into the fifth file.

It also takes 33 hours to run.

 

[repete]

Still a bit difficult to understand the problem virgo, but let me try to explain how I have interpreted your info.

Generally, it seems you are up for some redevelopment as you have a timing problem (33 hours is along time) and an accuracy problem (whatever your code is doing now, it's not giving you the right answer, so needs to be changed). So don't be afraid to thow out bits of code that you already have when you find it's inefficient.

Can you establish where the bulk of your 33 hours is spent in the code ? If you can, then thats an area you can consider to review. (not your primary concern I know, but 33 hours seems a long time )

Can you establish how far through your code you get BEFORE the data gets messed up ? What I mean is, is the data only messed up in the last (fifth) file ? If so, then thats the code that needs to be changed, the code that feeds the data into the fifth file.

From what you said, it seems that the last file has a set structure of rows (as well as of course columns [fields]) so that you possibly have a CASE statement in your code that controls the update of data into the fifth file.

If over time, your data has changed, you may need to change the conditions defined in the CASE statement.

If your rows are defining categories (or whatever) and your columns (except the category of course) are numerical data, and you have a large file (ie many records), if your code is trying to TOTAL the file on the category, then you want some free disk space for temporary files created behind the scenes, and patience, as this can take a while to complete. If this is the case, consider placing your data into separate files until after the TOTAL command is complete, and then combine the smaller files into one for your report.

Again, I am just guessing. Any further info on the problem might help towards a better solution for you.



Pete Bloomfield
Down Under

 

[dgrewe]

As Pete suggested throwing out code that takes 33 hours to run is no big loss.
I would remark it out instead but
I woud try this first and see if I could get it to work
And then rewirte the SQL statement from it
use main file
copy to sort1 one for field that makes MembersNew
copy to sort2 one for field that makes MembersOld
copy to sort3 one for field that makes SubscriberNew
copy to sort4 one for field that makes SubscriberReNew
copy stru to CrystalRptDbf
use CrystalRptDbf
append from sort1
append from sort2
append from sort3
append from sort4
index as required
print report
David W. Grewe
Dave@internationalbid.com
ICQ VFP ActiveList #46145644

 

[zimbaliza]

I think what you have is 4 Large DBF files all over
1 gig? and just trying to do one "Select" statement
to get all the data out....

(is my only idea why fox would take soo long to extract the data)....

I would, create sub cursors first, thin out the data,

you can do this by....

Select Invoice,;
Sku,;
"*" as Dummy;
From inv_Head;
into cursor Tmp_Head

the "*" will force fox into creating a real "table" not a filter, you will then be able to work of that... with your other databases... you can also... add 1 Index on the cursor
(which can help)

you just need to break it down... & it will go alot faster

May take a bit more coding, but you should be able to really narrow down the data....

Another really cool thing I like with Fox's SQL, is the fact you can put iif() statements into it... tends to make
your sql a little big... but it works..

Select store,;
sum(iif(sale_type="CASH",total,0)) as cash,;
sum(iif(sale_type="CHARGE",total,0)) as charge;
from salmonth ;
where.... etc etc etc...

sorry if I have dribbled on a bit.... I just can't work out how something can take 33 hours to run, I think our databases are big at 1.2+ gigs, but data comes off them quiet quickly without any real problems.

Anyway I hope these ideas help speed it up

Zim

 

[virgo]

Zim,
What are sub cursors?

Why did you make that statement about iif() and sql? Is there a special iif function?

Also, after I get all this information out, then I need to append it to specific places in another database.

Also, the 33 hours got involved with Access because people 'said that Foxpro could not do it'.

So, it was Foxpro, and then Access, and then Foxpro again to make up the total time.

However, through this, I am seeing that it would be much better just to use Foxpro.