I have several complex (the sql statement for it runs about 5 pages when copied to Word) Access make table queries which have the following calculated field:
COMPANYDOLLAR: [Gross_Commission_Amount]-([Amount_Paid_t
ther_Broker]+[Total_Listing_Referral_Payouts]+[Total_OTT_Referral_Payouts]+[Total_Selling_Referral_Payouts]+[CoExclusiveAgencyFees]+[TotalSellReportingGrossComm]+[TotalListReportingGrossComm])
The value COMPANYDOLLAR appears the calculations of a number of other calculated fields. I need to convert this query into a stored procedure, but can't use COMPANYDOLLAR in the other calculated fields of the SP. I tried using a view to calculate COMPANYDOLLAR with the idea of basing the the SP on the view and then using COMPANYDOLLAR for the SP calculations, but that didn't seem to work.
This if a very complex query (on of a series of complex action queries that create and load a table) so replacing COMPANYDOLLAR with its calculation in every place that it appears becomes prohibitive. I've thought of a user defined function (I'm using SQL Server 2000) but I would still have to show the parameters being fed in at each location of the function.
How do you deal with this kind of problem?
COMPANYDOLLAR: [Gross_Commission_Amount]-([Amount_Paid_t
ther_Broker]+[Total_Listing_Referral_Payouts]+[Total_OTT_Referral_Payouts]+[Total_Selling_Referral_Payouts]+[CoExclusiveAgencyFees]+[TotalSellReportingGrossComm]+[TotalListReportingGrossComm])The value COMPANYDOLLAR appears the calculations of a number of other calculated fields. I need to convert this query into a stored procedure, but can't use COMPANYDOLLAR in the other calculated fields of the SP. I tried using a view to calculate COMPANYDOLLAR with the idea of basing the the SP on the view and then using COMPANYDOLLAR for the SP calculations, but that didn't seem to work.
This if a very complex query (on of a series of complex action queries that create and load a table) so replacing COMPANYDOLLAR with its calculation in every place that it appears becomes prohibitive. I've thought of a user defined function (I'm using SQL Server 2000) but I would still have to show the parameters being fed in at each location of the function.
How do you deal with this kind of problem?
