if code is a PK try this:
lcCustPrsched='CU02'
lcContPrsched='CO03'
lcProdserv=INPUTBOX('Enter Product Code','Product Pricing','AGE100')
CREATE SQL VIEW vu_Pricing as ;
SELECT pricebase.prodserv ;
, prsched.baserate ;
, SUM(IIF(pricebase.code = ?lcCustPrsched ,pricebase.rate ,0.000) ) rate ;
, SUM(ROUND(IIF(pricesched.type=='%' ;
, rate*IIF(pricebase.code = ?lcCustPrsched ,pricesched.urgent * 0.01 ,1 );
, rate+IIF(pricebase.code = ?lcCustPrsched ,pricesched.urgent ,0.0)),2)) dprice1 ;
, SUM(ROUND(IIF(pricesched.type=='%' ;
, rate*IIF(pricebase.code = ?lcCustPrsched ,pricesched.general * 0.01 ,1 );
, rate+IIF(pricebase.code = ?lcCustPrsched ,pricesched.general ,0.0)),2)) dprice2 ;
, SUM(ROUND(IIF(pricesched.type=='%' ;
, rate*IIF(pricebase.code = ?lcCustPrsched ,pricesched.planned * 0.01 ,1 );
, rate+IIF(pricebase.code = ?lcCustPrsched ,pricesched.planned ,0.0)),2)) dprice3 ;
, SUM(ROUND(IIF(pricesched.type=='%' ;
, rate*IIF(pricebase.code = ?lcContPrsched ,pricesched.urgent * 0.01 ,1 );
, rate+IIF(pricebase.code = ?lcContPrsched ,pricesched.urgent ,0.0)),2)) cprice1 ;
, SUM(ROUND(IIF(pricesched.type=='%' ;
, rate*IIF(pricebase.code = ?lcContPrsched ,pricesched.general * 0.01 ,1 );
, rate+IIF(pricebase.code = ?lcContPrsched ,pricesched.general ,0.0)),2)) cprice2 ;
, SUM(ROUND(IIF(pricesched.type=='%' ;
, rate*IIF(pricebase.code = ?lcCustPrsched ,pricesched.planned * 0.01 ,1 );
, rate+IIF(pricebase.code = ?lcCustPrsched ,pricesched.planned ,0.0)),2)) cprice3 ;
FROM prsched ;
LEFT JOIN pricebase ON prsched.baserate == pricebase.basecode ;
LEFT JOIN pricesched ON prsched.code+pricebase.prodserv == pricesched.prsched+pricesched.prodserv ;
WHERE pricebase.prodserv = ?lcProdserv AND prsched.code IN (?lcCustPrsched,?lcContPrsched)
GROUP BY pricebase.prodserv
USE wots!vu_Pricing
BROWSE
