Hi

I have two views that I would like to combine into a singe view. I have tried various combinations to try and use the results of the first select in the second select but have not been able to get any subquery going.

Here are my views.

lcCustPrsched='CU02'
lcContPrsched='CO03'
lcProdserv=INPUTBOX('Enter Product Code','Product Pricing','AGE100')
CREATE SQL VIEW vu_Prices as ;
		SELECT prsched.code, ;
				prsched.baserate,;
				pricebase.rate, ;
				pricebase.prodserv,;
				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+pricebase.prodserv == pricesched.prsched+pricesched.prodserv ;
			WHERE prsched.code = ?lcCustPrsched AND pricebase.prodserv = ?lcProdserv ;
	Union ;
		SELECT prsched.code,;
				prsched.baserate,;
				0.000 as rate, ;
				pricebase.prodserv,;
				' ' 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+pricebase.prodserv == pricesched.prsched+pricesched.prodserv ;
			WHERE prsched.code = ?lcContPrsched AND pricebase.prodserv = ?lcProdserv

CREATE SQL VIEW vu_Pricing as ;
	SELECT prodserv, baserate, ;
		sum(rate) as rate, ;
		sum(ROUND(IIF(dtype='%', rate*dlevel1/100, rate+dlevel1),2)) as dprice1, ;
		sum(ROUND(IIF(dtype='%', rate*dlevel2/100, rate+dlevel2),2)) as dprice2, ;
		sum(ROUND(IIF(dtype='%', rate*dlevel3/100, rate+dlevel3),2)) as dprice3, ;
		sum(ROUND(IIF(ctype='%', rate*clevel1/100, rate+clevel1),2)) as cprice1, ;
		sum(ROUND(IIF(ctype='%', rate*clevel2/100, rate+clevel2),2)) as cprice2, ;
		sum(ROUND(IIF(ctype='%', rate*clevel3/100, rate+clevel3),2)) as cprice3 ;
	FROM wots!vu_prices ;
		GROUP BY prodserv

USE wots!vu_Pricing
BROWSE

Could someone advise whether or not I can create a single view?

regards
Geoff Scott