Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2002
    Posts
    45

    Unanswered: 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 */
    return
    Sent By Royal Mail

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: invalid column name ref: calculated columns

    Originally 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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2002
    Posts
    45
    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
    Sent By Royal Mail

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah You can't do that...

    USE Northwind
    GO

    SELECT CustomerId + Convert(varchar(10),EmployeeId) As A, A+'Cant be done!'
    FROM Orders
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2002
    Posts
    45
    surely there is a work around??!!!?!?!?!?!?
    Sent By Royal Mail

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  8. #8
    Join Date
    Aug 2002
    Posts
    45
    thankyou gentlemen
    there are no errors so far...
    now i wait till we have the data
    Sent By Royal Mail

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or create calculated columns for the combinations you need.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •