invalid column name ref: calculated columns

hello

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
cq

Alter Procedure AXACCBodereaux

(
@.ReportsPrintMenuHoldCoveredText73 datetime
)

As

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,

(Rating.[Total MD Premium] + Rating.TotaGITPrem + Rating.TotalmoneyPrem + Rating.DeteriorationofstocklimitPrem ) AS sum1,

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 */
returnOriginally posted by colonelquinn
hello

we have a slight problem.. we are upszing a access database to sql server and hence are rewriting all the queries as stored procedures.

You consider that a slight problem?

Also, I'm not sure I follow the problem...of course yuo can have derived data...what's the issue?|||derived data??
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
or
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.
any ideas
#
thanks very much
cq|||Yeah You can't do that...

USE Northwind
GO

SELECT CustomerId + Convert(varchar(10),EmployeeId) As A, A+'Cant be done!'
FROM Orders|||surely there is a work around??!!!?!?!?!?!?|||USE Northwind
GO

SELECT A + ' Can be done!' FROM (SELECT CustomerId + Convert(varchar(10),EmployeeId) As A FROM Orders) As xxx

But it looks like a lot of rework...since you're doing it anyway...|||just define a view with as many of the combinations as you wish -- a+b+c, d+e, etc.

then query the view instead of the table

rudy
http://r937.com|||thankyou gentlemen
there are no errors so far...
now i wait till we have the data|||...or create calculated columns for the combinations you need.

blindman