>I am using the following code in a VFP view to return customer information from a SQL server. This view includes references to SQL server stored procedures named getcustomerbalance and getcustomeramountdue. I'm trying to speed up this view and was wondering if I could make the call to getcustomeramountdue only if getcustomerbalance returns a value greater than zero. If so whats the best way to implement it?
>
>
>SELECT Customer.customerid, ;
> Customer.cCUSTNO, ;
> Customer.dENTERED, ;
> Customer.nCOBALANCE, ;
> Customer.dBILL, ;
> Customer.dLASTPMT, ;
> Customer.cREP, ;
> Customer.cLASTNAME, ;
> Customer.cFIRSTNAME, ;
> Customer.cTITLE, ;
> Customer.cADDRESS, ;
> Customer.cCITY, ;
> Customer.cSTATE, ;
> Customer.cZIP, ;
> Customer.cPHONE, ;
> Customer.cPHONE2, ;
> Customer.cPHONE3, ;
> Customer.cCONTACT, ;
> Customer.mNOTES, ;
> Customer.cTAXNO, ;
> Customer.cIDNO, ;
> Customer.cIDDESC, ;
> Customer.dNEXTPMT, ;
> Customer.nTAX, ;
> Customer.lCharge_Off, ;
> Customer.cfinanceno, ;
> CAST((RTRIM(Customer.clastname)+', '+Customer.cfirstname) AS CHAR(68)) AS cfullname, ;
> CAST((RTRIM(Customer.cfirstname)+' '+Customer.clastname) AS CHAR(68)) AS cprtname, ;
> Dbo.getcustomerbalance(Customer.cfinanceno) AS qybalance, ;
> Dbo.getcustomeramountdue(Customer.cfinanceno) AS qyamountdue, ;
> Dbo.GetPmtsInvoiced(Customer.cfinanceno) AS nPmtsInvoiced;
> FROM dbo.Customer Customer ;
> ORDER BY 26
>
>Thanks In Advance
>
>Jeff
Instead of 3 Scalar-valued functions use one table valued function and join with it.
If it's not broken, fix it until it is.
My Blog