Unanswered: invalid column name ref: calculated columns
we have a slight problem.. we are upszing a access database to sql server and hence are rewriting all the queries as stored procedures.
we are trying to use a already selected feild in a calculation to return another feild or column as they like to be known. this as we have found is not allowed!! is there anyone who knows of a clever work around so we dont have to write the calculation out 10000000000.... times..
below is the code with the problem areas separted ta
Alter Procedure AXACCBodereaux
SELECT QuoteRegister.QuoteNumber, QuoteRegister.PolicyNumber, QuoteRegister.Datecoverfrom, QuoteRegister.Datecoverto,
QuoteRegister.Broker, QuoteRegister.CLNTName, CLNTMaster.CLNTAddress1, CLNTMaster.CLNTAddress2, CLNTMaster.CLNTAddress3,
CLNTMaster.CLNTAddress4, CLNTMaster.CLNTPostcode, CLNTMaster.CLNTTradelongname,
Case Rating.ReasonForIssueCode When 'NBC' Then 'NB' Else 'RNL' end AS Type,
QuoteRegister.[BuildingSI] + QuoteRegister.[Total ContentsSumInsured] AS [Material Damage], Rating.TotalBI, Rating.PLIndemnitylimit,
percentnonliab = Case When Rating.TotalExcludingliabs <>0 Then Rating.KeyednonliabsdiscAmount / Rating.TotalExcludingliabs Else 0 end,
percentliab = Case When Rating.Totalliabilities <>0 Then Rating.KeyedliabilitiesdiscountAmount / Rating.Totalliabilities Else 0 end,
MDPREM = Case When Rating.KeyednonliabsdiscAmount <>0 Then ( sum1 - ( sum1 * percentnonliab))
Else sum1 - sum1 *( Rating.NonliabsdiscountPercent/100) end ,
TOTBIPREM = Case When Rating.KeyednonliabsdiscAmount <>0 Then Rating.TotalBIPrem - Rating.TotalBIPrem * percentnonliab
Else Rating.TotalBIPrem - Rating.TotalBIPrem * (Rating.NonliabsdiscountPercent/100) end ,
PLPREM = Case When Rating.KeyedliabilitiesdiscountAmount <>0 Then (Rating.TotalPLPrem + Rating.TotalPRPrem) -
(Rating.TotalPLPrem + Rating.TotalPRPrem) * percentliab Else (Rating.TotalPLPrem + Rating.TotalPRPrem) -
(Rating.TotalPLPrem + Rating.TotalPRPrem)*(Rating.Totalliabilitiesdiscou ntPercent/100) end ,
ELPREM = Case When Rating.KeyedliabilitiesdiscountAmount <>0 Then Rating.TotalELPrem-(Rating.TotalELPrem*(percentliab))
Else Rating.TotalELPrem - Rating.TotalELPrem*(Rating.Totalliabilitiesdiscoun tPercent/100) end ,
QuoteRegister.[Total Premium], (QuoteRegister.[Total Premium]*0.275) AS Commission,
(QuoteRegister.[Total Premium]*0.05) AS IPT, ([Total Premium]+IPT-Commission) AS NIA,
0 AS Finalised, QuoteRegister.Proposalreceived, QuoteRegister.CreationDate, QuoteRegister.CLNTCode,
QuoteRegister.DateonHoldCoveredBDX, Rating.KeyedliabilitiesdiscountAmount, Rating.KeyednonliabsdiscAmount
FROM (QuoteRegister INNER JOIN CLNTMaster ON QuoteRegister.CLNTCode =
CLNTMaster.CLNTCode) INNER JOIN Rating ON (CLNTMaster.CLNTCode = Rating.CLNTCode) AND
(QuoteRegister.QuoteNumber = Rating.QuoteNumber)
WHERE (((Case Rating.ReasonForIssueCode When 'NBC' Then 'NB' Else 'RNL' end ) <> 'MTA') AND ((QuoteRegister.DateonHoldCoveredBDX)=
@ReportsPrintMenuHoldCoveredText73) AND ((QuoteRegister.ReasonForIssueCode) <> 'MTA') AND
((QuoteRegister.Insurer)='AXACC') AND ((Rating.QuoteNumber)=QuoteRegister.QuoteNumber) AND
((CLNTMaster.CLNTCode)=QuoteRegister.CLNTCode) AND ((Rating.ReasonForIssueCode)<>'MTA'));
/* set nocount on */
sorry dont know what that is only started doing this on friday..
to simplify the problem if you do this
select a ,b, c , a+b+c as d
select a,b,c, d+e as f , f+g as h
errors fly out saying any pre referenced value are invalid column names.
of course i could type out the calculation in every place but when i come to change the calc i am bound to forget to do it in every place.
i have tried doing two sql queies within the procedure but that gives the same error.
thanks very much