Hi Al

You are probably right. I wonder how MSSQL determines the same when is not true with respect to declared variables?

It is a real pity 'cos the actual command I want to use is somewhat more complex (see below). As you can see there is a high degree of dependancy. As this is a process that will only be used for a short period of time I will split it out into a number of passes just to be on the safe side.

regards
Geoff

UPDATE invcont SET ;
	dt_t_resp = IIF(ISNULL(kpitype.response) OR kpitype.response = 0, ;
					.NULL., ;
					goCalendar.CalcForwardDatetime(dt_t_St, kpitype.response, kpitype.r_freq, kpitype.r_tod)), ;
	dt_t_w1 = IIF(ISNULL(kpitype.response) OR kpitype.response = 0, ;
					.NULL., ;
					goCalendar.CalcForwardDatetime(IIF(kpitype.r_warn > 0, dt_t_St, dt_t_Resp), kpitype.r_warn, kpitype.r_w_freq, kpitype.r_w_tod)), ;
	dt_t_Comp =  IIF(ISNULL(kpitype.complete) OR kpitype.complete = 0, ;
					.NULL., ;
					goCalendar.CalcForwardDatetime(IIF(kpitype.complete > 0, dt_t_St, dt_Response), ABS(kpitype.complete), kpitype.c_freq, kpitype.c_tod)), ;
	dt_t_W2 =  IIF(ISNULL(kpitype.complete) OR kpitype.complete = 0, ;
					.NULL., ;
					goCalendar.CalcForwardDatetime(IIF(kpitype.c_warn > 0, .dt_t_St, .dt_t_Comp), kpitype.c_warn, kpitype.c_w_freq, kpitype.c_w_tod)), ;
	dt_t_Inv =  IIF(ISNULL(kpitype.invoice) OR kpitype.invoice = 0, ;
					.NULL., ;
					goCalendar.CalcForwardDatetime(IIF(kpitype.invoice > 0, dt_t_St, dt_w), ABS(kpitype.invoice), kpitype.i_freq, kpitype.i_tod)), ;
	dt_t_W3 = IIF(ISNULL(kpitype.invoice) OR kpitype.invoice = 0, ;
					.NULL., ;
					goCalendar.CalcForwardDatetime(IIF(kpitype.i_warn > 0, dt_t_St, dt_t_Inv), kpitype.i_warn, kpitype.i_w_freq, kpitype.i_w_tod)), ;
	mn_resp = IIF(ISNULL(dt_response), ;
					.NULL., ;
					IIF(INLIST(kpitype.r_Freq, 1, 4), ;
						goCalendar.DatetimeInterval2(dt_t_st, dt_response, kpitype.r_freq, 60), ;
						goCalendar.DateInterval2(dt_t_st, dt_response, kpitype.r_freq))), ;
	mn_resp = IIF(ISNULL(dt_response), ;
					.NULL., ;
					IIF(INLIST(kpitype.r_Freq, 1, 4), ;
						goCalendar.DatetimeInterval2(dt_t_st, dt_response, kpitype.r_freq, 60), ;
						goCalendar.DateInterval2(dt_t_st, dt_response, kpitype.r_freq))), ;
	mn_comp = IIF(ISNULL(dt_w), ;
					.NULL., ;
					IIF(INLIST(kpitype.c_Freq, 1, 4), ;
						goCalendar.DatetimeInterval2(dt_t_st, dt_w, kpitype.c_freq, 60), ;
						goCalendar.DateInterval2(dt_t_st, dt_w, kpitype.c_freq))), ;
	mn_inv = IIF(ISNULL(dt_j), ;
					.NULL., ;
					IIF(INLIST(kpitype.i_Freq, 1, 4), ;
						goCalendar.DatetimeInterval2(dt_t_st, dt_j, kpitype.i_freq, 60), ;
						goCalendar.DateInterval2(dt_t_st, dt_j, kpitype.i_freq))), ;
	kt_outlyer = IIF(kpitype.o_compnt = 1,;
				 	CalcResult(mn_resp, ,kpitype.o_calc, kpitype.o_min, kpitype.o_max), ;
					IIF(kpitype.o_compnt = 2,;
						 	CalcResult(mn_comp, ,kpitype.o_calc, kpitype.o_min, kpitype.o_max), ;
						 	0)) ;
FROM invcont ;
	JOIN kpitype ON kpitype.type = invcont.kpi

>>Hi
>>
>>If I have two fields being changed in an update or replace statement is it valid to rely on a the changed value of the first field in the statement when setting the second or should I always do two passes?
>
>eg.

UPDATE mytable SET ;
>	field1 = CalcForwardTime(), ;
>	field2 = OtherCalc(field1)

>
>An interesting question. I don't know the answer to this particular question but the following points can be made:
>
>- VFP most of the time guarantees left-to-right execution in logical expressions (I know this is not what we're discussing but bear with me)
>
>- At least one exception to the above has been found, and it's in the SQL engine:
>Always stops evaluating if..? Thread #1041159 Message #1041159
>
>- It is believed that the above anomaly occurs because the VFP SQL engine is non-procedural; the parser and/or query optimizer can rearrange the query as it sees fit in order to improve performance
>
>With the above in mind we can say:
>
>- Since logical expression LTR is guaranteed in other areas of the language there is real incentive to support it in the SQL engine, but it is not
>
>- There is no theoretical reason in set-based (SQL) logic to always update one column in a list before any other. It's even possible that some rows could update field2 based on the old version of field1, and others using the new version
>
>Since the VFP SQL engine doesn't support LTR for logical expressions I see even less reason why it should support "LTR" in the list of columns being updated.
>
>Having said all that, it may actually be supported in VFP (or a particular version or SP):
>
>- The parser may actually recognize that field2 depends on field1 and make sure it is always updated second. I consider this possibility very unlikely because it makes the parsing very complex very quickly; it's easy to create examples where you have circular references i.e. field2 relies on field1 AND field1 relies on field2
>
>- There may be no theoretical or performance reason to update columns in an order other than as specified in the original query, so the VFP team may have implemented LTR column processing by default
>
>I'd suggest that these two questions can only be answered by the VFP Team. And even if it turns out that you can rely on LTR column updates in your current version of VFP, it strikes me as one of the classic examples of something that shouldn't be relied on because it could change in the future.
>
>Personally, I wouldn't rely on it.