Selecting a record that is updating


08-Feb-2021 04:09

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 intset @curid = 1Select @maxid = max(id1) from #test9while @maxid @curidbegin 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 urs04dbo.ai_enc where conno = @curconno and serv_cat = @curservcat and program = @curprog and act_dt = @cur1yrstartdt) update #test9 set act_enc = @curactenc where id1 = @curid endelse begin set @curactenc = (select count(act_id) from urs04dbo.ai_outr where conno = @curconno and serv_cat = @curservcat and program = @curprog 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 urs04dbo.ai_enc where conno = @curconno and serv_cat = @curservcat and program = @curprog 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 endelse begin set @curactclients = (select (sum(total) sum(total_unkn)) from urs04dbo.ai_outr where conno = @curconno and serv_cat = @curservcat and program = @curprog and act_dt = @cur1yrstartdt) update #test9 set act_clients = @curactclients where id1 = @curid endset @curactenc = 0set @curactclients = 0set @curid = @curid 1enddelete from urs04wcl01.projections_vs_actualsinsert into urs04wcl01.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 #test9where [1y_conno] is not null END 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: 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.

DO WHILE maxid = curid SELECT era_proj_actuals locate for id1 = curid curprog = prog_id curservcat = serv_catcur1yrstartdt = oneyrstartdtcur1yrenddt = oneyrenddtcurconno = conno IF curservcat in ('00015', '00016', '00017', '00018', '00019', '00043', '00044', '00047', '00048') thencuractenc = (select COUNT(act_id) from ai_enc where conno = curconno and serv_cat = curservcat and 'program' = curprog and act_dt It would be easier to understand, if you come over with the SQL foxpro can't execute.

selecting a record that is updating-64

sex dating in weeks louisiana

I ended up going a different direction to get the same results. Mike __________________________________Mike Lewis (Edinburgh, Scotland)Visual Fox Pro articles, tips, training, consultancy Bad form to use CLOSE TABLES so willy-nilly, too.IF INLIST(curservcat, "00015", "00016", "00017", "00018", "00019", "00043", "00044", "00047", "00048") = . 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. What I'm attempting to do is select a group of records and count on a particular field within those records.

selecting a record that is updating-23

Kannada live webcamara dating registration free

Then update a particular field on a particular record within a table.

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.