Having trouble selecting records based on variables and updating table

[WCL01]

I'm very new to foxpro so please be kind.
What I'm attempting to do is select a group of records and count on a particular field within those records. Then update a particular field on a particular record within a table. All of this sits inside of a do while loop with a counter to go through all the records. I'm able to do this in sql without any problems but very little translates over well from sql to foxpro.

DO WHILE maxid >= curid

SELECT era_proj_actuals
locate for id1 = curid
curprog = prog_id
curservcat = serv_cat
cur1yrstartdt = oneyrstartdt
cur1yrenddt = oneyrenddt
curconno = conno

IF curservcat in ('00015', '00016', '00017', '00018', '00019', '00043', '00044', '00047', '00048') then
curactenc = (select COUNT(act_id) from ai_enc where conno = curconno and serv_cat = curservcat and 'program' = curprog and act_dt <= curoneyrstartdt and act_dt >= curoneyrenddt)
UPDATE era_proj_actuals SET act_enc = curactenc WHERE id1 = curid
ENDIF

curid = curid + 1
enddo

 

[Olaf Doschke]

It would be easier to understand, if you come over with the SQL foxpro can't execute.

One thing I spot is you trying to set a variable to an sql result. That's not possible. An SQL ends in a resultset, even select count() dfoes not result in a single scalar value. So do

select count(*) as nCount ...from... into cursor curResult
curactenc = curResult.nCount

Besides that you can't do
IF var in (list of values)

What you can do instead is
IF inlist(var, list of values)

Besides I don't see a rough error. You could also check IF FOUND() after LOCATE, to make sure you found an id, otherwise you end up doing things on EOF() of era_proj_actuals, which could lead to unwanted values of all the variables you set and use in the further SQL.

On the other side, to demonstate a bit what SQL works:

update prodcuts set ordercount = computed.ordercount;
from (Select Count(*) as ordercount, product_id From order_line_items group by product_id;
where  product_id in (15,10,54)) as computed;
where products.product_id = computed.product_id

That shows you can actually do an update of a table with values you compute in a subselect from another table.

Bye, Olaf.

 

[WCL01]

This is the sql I'm trying to replicate Olaf. I'm trying to start off small with the one If statement and then once I have that working replicate to the others that I have.

declare @curid int, @maxid int, @curprog varchar(5), @curservcat varchar(5),
@curconno varchar(10), @cur1yrstartdt datetime, @cur1yrenddt datetime, @curactenc int, @curactclients int
set @curid = 1

Select @maxid = max(id1) from #test9

while @maxid > @curid
begin
select @curprog = prog_id,@curservcat = serv_cat, @curconno = [5yr_conno],
@cur1yrstartdt = [1yr_startdt], @cur1yrenddt = [1yr_enddt]
from #test9
where id1 = @curid

If @curservcat not in ('00015', '00016', '00017', '00018', '00019', '00043', '00044', '00047', '00048')
begin set @curactenc = (select count(act_id) from urs04.urs.dbo.ai_enc where conno = @curconno and
serv_cat = @curservcat and program = @curprog and act_dt <= @cur1yrenddt and
act_dt >= @cur1yrstartdt)
update #test9 set act_enc = @curactenc where id1 = @curid end
else
begin set @curactenc = (select count(act_id) from urs04.urs.dbo.ai_outr where conno = @curconno and
serv_cat = @curservcat and program = @curprog and act_dt <= @cur1yrenddt and
act_dt >= @cur1yrstartdt)
update #test9 set act_enc = @curactenc where id1 = @curid end

If @curservcat not in ('00015', '00016', '00017', '00018', '00019', '00043', '00044', '00047', '00048')
begin select tc_id into #test33 from urs04.urs.dbo.ai_enc where conno = @curconno and
serv_cat = @curservcat and program = @curprog and act_dt <= @cur1yrenddt and
act_dt >= @cur1yrstartdt
group by tc_id
set @curactclients = (select count(tc_id) from #test33)
drop table #test33
update #test9 set act_clients = @curactclients where id1 = @curid end
else
begin set @curactclients = (select (sum(total) + sum(total_unkn)) from urs04.urs.dbo.ai_outr where conno = @curconno and
serv_cat = @curservcat and program = @curprog and act_dt <= @cur1yrenddt and
act_dt >= @cur1yrstartdt)
update #test9 set act_clients = @curactclients where id1 = @curid end

set @curactenc = 0
set @curactclients = 0
set @curid = @curid +1
end
delete from urs04.urs.wcl01.projections_vs_actuals
insert into urs04.urs.wcl01.projections_vs_actuals select projectionsid, contract_id, prog_id,
serv_cat, serv_enc, serv_clients, [1y_conno], [1yr_startdt],[1yr_enddt], fiveyrcontractid,
clients, [5yr_conno], act_enc, act_clients from #test9
where [1y_conno] is not null
END

 

[JRB-Bldr]

I'm very new to foxpro so please be kind.

The first thing I recommend for anyone who is New to Visual Foxpro is to spend some time going through the free on-line VFP tutorials at:

http://www.garfieldhudson.com/FreeVideos.aspx

Not only may it help with current challenges, but also it might help with future efforts.

As to your specific question, there are a lot of 'unknowns' for us and possibly too many for us to make guesses about.

Things like:
* where does maxid 'live'
* where is curid initialized before going into the DO/ENDDO loop?
* is the data table ai_enc already open before the DO/ENDDO loop?
* and others

You do not tell us where your code above 'falls apart'.
Does it throw an error message or just not give you the answer you want?

Perhaps with more clarity we can assist you better.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

I expected the original SQL being a single complexer Update SQL. OK, but then all is just a matter of a bit of differences in how you get the select result into your variables, and I already showed that.

Once you corrected both how you set curactenc and how you do the IF in foxpro with [inlist(var,list)] instead of [var in (list)], see what other problems remain and come back.

Bye, Olaf.

 

[Mike Lewis]

WCL,

You don't say which SQL dialect you are moving from. But judging by the syntax of your example, it looks like Microsoft SQL Server (T-SQL).

If that's true, you might be interested in this article, which compares the syntax of the two dialects:
SQL SELECT in VFP and T-SQL.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[WCL01]

Thanks for all the help everybody. I ended up going a different direction to get the same results.

IF INLIST(curservcat, "00015", "00016", "00017", "00018", "00019", "00043", "00044", "00047", "00048") = .F. then
USE ai_enc
SET FILTER TO 'program' = curprog .and. serv_cat = curservcat .AND. act_dt >= cur1yrstartdt .AND. act_dt <= cur1yrenddt .AND. conno = curconno
COUNT TO curactenc
SET FILTER TO
CLOSE TABLES
UPDATE era_proj_actuals SET enc_count = curactenc WHERE id1 = curid
curactenc = 0

I decided to try and stick with the little I know when it comes to foxpro and the stuff I currently run in runfox. The only thing I'm curious about is the spot in my set filter to command where I say act_dt >= cur1yrstartdt do I need to put {} around the cur1yrstartdt due to it containing a date?

 

[Mike Lewis]

A couple of points:

- No, you don't need to put {} round the date field. Those delimiters are only used for date literals - not field names, variables, etc.

- It's not a good idea to execute USE within a loop. USE closes the table and re-opens it. It's better to USE it before you start the loop, and simply keep it open after that. However, you might need to use the SELECT statement to make it the "current" table (note that this use of SELECT has got nothing to do with SELECT in the SQL sense).

Apart from that, it looks like you're on the right track.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

Bad form to use CLOSE TABLES so willy-nilly, too.

The use of SET FILTER is not a best practice, particularly since you appear to be using it solely for the COUNT. It isn't needed for that and can be a huge performance problem. Just filter the count directly:

   COUNT TO curactenc FOR ;
     'program' = curprog .and. serv_cat = curservcat .AND. act_dt >= cur1yrstartdt .AND. act_dt <= cur1yrenddt .AND. conno = curconno

 

[WCL01]

danfreeman thanks for that. Is there a way to count unique records based on a particular field within that record by chance?

 

[Dan Freeman]

Is there a way to count unique records based on a particular field within that record by chance?

The literal answer is "yes". ;-)

But since it's unclear to me what you're actually after, I can't go any further than that. If you want to know "how many records where fieldx="Some Value" then you'd use count.

However, when some people say "count for unique" what they're really looking for is duplicates. That's a different kettle of fish and can't really be done with Count. (It can be done, just not with Count.)

Which kettle is yours?

 

[WCL01]

My kettle is the second kettle you talked about. What I'm attempting to do is filter out a bunch of records while every record will be different in one way shape or form there is still this one field called TC_ID that can duplicate. There may be 2000 records once filtered however there are only 1500 distinct tc_id's in all 2000 records. I want to be able to find out the number of distinct tc_id's in the 2000 records. I currently do it this way but as you stated using set filter is not the best way to do it.

IF INLIST(curservcat, "00015", "00016", "00017", "00018", "00019", "00043", "00044", "00047", "00048") = .F. then
USE ai_enc
SET FILTER TO 'program' = curprog .and. serv_cat = curservcat .AND. act_dt >= cur1yrstartdt .AND. act_dt <= cur1yrenddt .AND. conno = curconno
SET UNIQUE on
INDEX on tc_id TO tic
COUNT TO curactclients
SET FILTER TO
CLOSE TABLES
UPDATE era_proj_actuals SET c_count = curactclients WHERE id1 = curid

This does give me the unique count of tc_id. But if there is a better way of doing I would love to hear it.

 

[Dan Freeman]

Again, you should get out of the habit of always opening and closing tables and SET FILTER is almost never the right way to go.

I want to be able to find out the number of distinct tc_id's in the 2000 records.

See, that's different than what you asked at first. This will tell you the number of distinct TC_ID's:

SELECT DISTINCT TC_ID ;
  FROM YourSource
? _Tally

The "in the 2000" is your previous filter condition which can be applied to this query too:

SELECT DISTINCT TC_ID ;
  FROM YourSource ;
 WHERE 'program' = curprog .and. serv_cat = curservcat .AND. act_dt >= cur1yrstartdt .AND. act_dt <= cur1yrenddt .AND. conno = curconno
lcValue = _Tally
?lcValue

Use Replace or Update to put lcValue where you need it.

I don't think that's what you're after either, going from your code example and other things you've said. But we're getting closer!

 

[WCL01]

Actually Danfreemen that worked out perfect. I understand that I need to get away from using the filter command and that is what I'm doing one section at a time thanks to your help. I was using the filter command in 4 spots and now I'm down to 1. My final and last set filter is below.
What I'm trying to do is select all the records that meet my criteria and sum up 2 fields within them. I currently use the set filter command declare an array and then sum up the two fields into the array and add them together.
As far as the close tables goes I was doing that in hopes not to lock up those tables on somebody that might be trying to get into them. I'm writing this for an existing foxpro program where people are in these tables all the time so my thought process was to close them as soon as I was done with them. Is there a setting a I can turn on that will prevent the tables from being locked or allow me to select the records within the table if they have them open?

USE ai_outr
SET FILTER TO 'program' = curprog .and. serv_cat = curservcat .AND. act_dt >= cur1yrstartdt .AND. act_dt <= cur1yrenddt .AND. conno = curconno
DECLARE alltotal[2]
SUM Total, Total_unkn TO array alltotal
curactclients = alltotal[1] + alltotal[2]
SET FILTER TO
CLOSE TABLES
UPDATE era_proj_actuals SET c_count = curactclients WHERE id1 = curid
curactclients = 0

Once again thanks for you help. I have learned a lot in this post.

 

[Olaf Doschke]

USE sometable SHARED will open a table in a way the file can also be opened by others, as it is, well, shared. Even better SET EXCLUSIVE OFF, then all USE will use shared. And last not least, if you do SQL, tables are opened, you don't need to do that.

The default setting of the VFP IDE for the EXCLUSIVE setting ON is to open data exclusive, but it's not the default for an EXE. The latter makes me wonder why you would have a problem. Are you doing things from within the VFP IDE?

In regard of your code, this would boil down to:

set exclusive off
* USE ai_outr SHARED

Select Sum(Total + Total_unkn) as nTotalsum;
FROM ai_outr;
WHERE 'program' = curprog ;
.AND. serv_cat = curservcat ;
.AND. act_dt >= cur1yrstartdt ;
.AND. act_dt <= cur1yrenddt ;
.AND. conno = curconno;
Into Cursor curResult

curactclients = NVL(curResult.nTotal,0)
CLOSE TABLES

UPDATE era_proj_actuals SET c_count = curactclients WHERE id1 = curid
curactclients = 0

Bye, Olaf.

 

[Olaf Doschke]

Are you sure about 'program' = curprog?

your comparing curprog (which I assume from your earlier code is a variable) with a string. A filter condition should filter some table field, shouldn't it?

If progam is a table field you still don't need extra delimiters just because it's a reserved word.

Bye, Olaf.

 

[WCL01]

It does work with 'program' = curprog and thank you for the help Olaf.