Hey Sergey

I got the view going as a subquery but I have one small problem which I am hoping is not a bug.

Basically, the problem is that when I change the underlying data to my view I get a "View definition has been change" message.

Below is my view definition. The intermediate stage was necessary because it was not calculating correctly. Requerying works fine until I change the data. I can get around it by closing the view and re-opening it but it seems to me there is something fundamental wrong here.

I'm a bit mystified as to how changing the data can constitute a "view definition change". Do you think it is a bug?

CREATE SQL VIEW v_Pricing as ;
	SELECT p_key, ;
		ROUND(IIF(dtype='%', custrate*dlevel1/100, custrate+dlevel1),2) as dprice1, ;
		ROUND(IIF(dtype='%', custrate*dlevel2/100, custrate+dlevel2),2) as dprice2, ;
		ROUND(IIF(dtype='%', custrate*dlevel3/100, custrate+dlevel3),2) as dprice3, ;
		ROUND(IIF(ctype='%', custrate*clevel1/100, custrate+clevel1),2) as cprice1, ;
		ROUND(IIF(ctype='%', custrate*clevel2/100, custrate+clevel2),2) as cprice2, ;
		ROUND(IIF(ctype='%', custrate*clevel3/100, custrate+clevel3),2) as cprice3 ;
	FROM ;
		(SELECT p_key, ;
			MAX(custrate) as custrate, ;
			MAX(dtype) as dtype, ;
			sum(dlevel1) as dlevel1, ;
			sum(dlevel2) as dlevel2, ;
			sum(dlevel3) as dlevel3, ;
			MAX(ctype) as ctype, ;
			sum(clevel1) as clevel1, ;
			sum(clevel2) as clevel2, ;
			sum(clevel3) as clevel3 ;
		FROM ;
		(SELECT prsched.code, ;
			prsched.baserate as custbase,;
			SPACE(4) as contbase,;
			pricebase.rate as custrate, ;
			0.000 as contrate, ;
			pricebase.prodserv,;
			 pricesched.p_key, ;
			pricesched.type as dtype, ;
			pricesched.urgent as dlevel1, ;
			pricesched.general as dlevel2, ;
			pricesched.planned as dlevel3, ;
			' ' as cType,;
			pricesched.urgent*0 as clevel1, ;
			pricesched.general*0 as clevel2, ;
			pricesched.planned*0 as clevel3 ;
		FROM prsched ;
			LEFT JOIN pricebase ON prsched.baserate == pricebase.basecode ;
			LEFT JOIN pricesched ON prsched.code == pricesched.prsched AND ;
				pricebase.p_key == pricesched.p_key ;
		WHERE prsched.code = ?lcCustPrsched AND pricebase.p_key = ?lnP_key ;
		UNION ;
		SELECT prsched.code,;
			SPACE(4) as custbase,;
			prsched.baserate as contbase,;
			0.000 as custrate, ;
			pricebase.rate as contrate, ;
			pricebase.prodserv,;
			pricesched.p_key, ;
			' ' as dType,;
			pricesched.urgent*0 as dlevel1, ;
			pricesched.general*0 as dlevel2, ;
			pricesched.planned*0 as dlevel3, ;
			pricesched.type as cType,;
			pricesched.urgent as clevel1, ;
			pricesched.general as clevel2, ;
			pricesched.planned as clevel3 ;
		FROM prsched ;
			LEFT JOIN pricebase ON prsched.baserate == pricebase.basecode ;
			LEFT JOIN pricesched ON prsched.code == pricesched.prsched AND ;
							pricebase.p_key == pricesched.p_key ;
		WHERE prsched.code = ?lcContPrsched AND pricebase.p_key = ?lnP_key ;
		) as tPrices ;
		GROUP BY p_key ;
		) as tPricing

>Geoff,
>
>VFP9 expected to be released at the end of the year but you can download public beta now and see if such subquery will work in it.
>
>>
>>Any idea when the final release of VFP9 is going to be?
>>