help with creating an sql statement  
Author Message
thereisnomike





PostPosted: Visual FoxPro General, help with creating an sql statement Top

Hi there - I'm kinda new at foxpro and I'm havin a bit of a hard time figuring out an sql statement to run what it is that I want to do. I'll try and explain this the best way I can. I'm creating a program to write a simple "datacheck" report to a text file that basically grabs 5 records based on a unique recipient group. I've got it working - but it's slow and I know that there is a much better way to do it than I'm doing. This is the code that grabs the records. The code is below:

for i = 1 to alen(aDC1) && aDC1 is an array with the unique recipient groups
use temp excl && temp is a table I'm using to manipulate the data w/o touching the original
index on recp_grpid to ord
copy to temp1 for recp_grpid = aDC1(i)
clos tables all
use temp1 excl
count to aNum(i) && this is where I store the counts of each individual recipient group
go top
dele next 5
use W:\SETUP\stru excl && just a structure I'm using to grab what I need
append from temp1 fields offer_id, recp_grpid, prop_id, guest_id for deleted()
recall all
clos indexes all
clos tables all
dele file "ord.idx"
dele file "temp1.dbf"
endfor

Now I know that this is horrible - but it's all I could figure out in the short time I had (I'm new at this). And of course (as I'm sure you know if you're reading this) the reason it is so slow is that is has to go through the database SOO many times just to get what it needs. Can someone help me with a point in the right direction to use an sql statement that does the same thing Much appreciated.



Visual FoxPro2  
 
 
CetinBasoz





PostPosted: Visual FoxPro General, help with creating an sql statement Top

Suppose your code was the way to do it (I don't mean it is:). Then lets first rectify it a bit for speed (so next time for a similar task you would have food for thought).
First prefix your memory variables with m. so VFP knows that you're referring to a memory variable and thus avoid:
1) Time spent to understand it (more important within loops - like for...endfor)
2) Prevent errors that might arise from name clashes between a field name and variable (field variables have higher precedence).
A human could easily say with aDC1(i) or aNum(i) you mean "i" is a memory variable but you know computers:) Also declare your variables. In VFP the declaration you almost always use is local so for example you'd declare "i" as:
local i
(I'm not writing the help file in detail but trying to give basics in a nutshell - ie: when I say "the declaration you almost always use is local" it means though there are other type of declarations in practice they are used once or maybe twice in a big application).
Of course declare your arrays too (however in local array myArray[nSize] declaration there are things you should know before you do that - behavior of resizing etc differs with commands, skipping it now).
Next move commands that doesn't need to be repeated out of loop. ie:

use temp excl && temp is a table I'm using to manipulate the data w/o touching the original
index on recp_grpid to ord
*...
clos tables all
*...
clos indexes all
clos tables all
dele file "ord.idx"
dele file "temp1.dbf"
endfor

is unnecessarily repeated in the loop. Instead it might look like:

use temp excl && temp is a table I'm using to manipulate the data w/o touching the original
index on recp_grpid to ord
for i = 1 to alen(aDC1) && aDC1 is an array with the unique recipient groups
select temp
copy to temp1 for recp_grpid = aDC1(i)
use temp1 in 0 && study "in 0" and select < alias >
select temp1
*...
endfor
clos tables all
dele file "ord.idx"
dele file "temp1.dbf"


Yet another unnecessary code:

use temp1 excl
count to aNum(i) && this is where I store the counts of each individual recipient group
go top
dele next 5
use W:\SETUP\stru excl && just a structure I'm using to grab what I need
append from temp1 fields offer_id, recp_grpid, prop_id, guest_id for deleted()
recall all

As I understand your intention in deleting them is to know which 5 records to append. ie:

use temp1 excl
count to aNum(i) && this is where I store the counts of each individual recipient group
use W:\SETUP\stru excl && just a structure I'm using to grab what I need
lnRecords = reccount()
append from temp1 fields offer_id, recp_grpid, prop_id, guest_id while reccount() < m.lnRecords + 5


OK after saying all these here is how I would do that instead:


create cursor recgroups (id i) && I assumed ids were integer
dimension aDC1[alen(aDC1),1] && append from array needs this, otherwise it would think it is just 1 record with n columns
append from array aDC1

select temp.*, sys(2015) as marker from temp ;
inner join recgroups on temp.recp_grpid == recgroups.id ;
order by temp.recp_grpid ;
into cursor crsTemp

select cnt(*) from crsTemp group by recp_grpid into array aNum

select * from crsTemp t1 ;
where marker in ;
(select top 5 marker from crsTemp t2 where t1.recp_grpid == t2.recp_grpid) ;
into cursor crsTemp1

select 0
use W:\SETUP\stru excl && just a structure I'm using to grab what I need
append from dbf("crsTemp1") fields offer_id, recp_grpid, prop_id, guest_id

close data all

PS: Untested code off the top of my head. Test first.