Post reply

Calculated Field - Invalid Column Name Error

  • January 10, 2007 at 7:43 am

    #175449

    Hi,

    We're using a SQL Server database in the place of a legacy Access system and though I've create numerous Access queries, I'm still learning the differences in writing SQL Server queries.

    I get an error whenever I use a calculated field ("TotalAmt") in another calculation as shown below. Would appreciate any help in getting this done.

    Select ........, ..., ...,

    sxsd_curr_accum_trxns

    *sxsd_chrg_rate AS TotalAmt,

    Round

    (TotalAmt*0.75*0.8,2) AS RoyaltyAmt,

    DateDiff

    (mm,sxsd_svc_startdttm, sxsd_svc_enddttm)+1 AS RoyaltyPeriod

    From .......

    The query is parsed without any errors, but the following error occurs on execution:

    Msg 207, Level 16, State 3, Line 5

    Invalid column name 'TotalAmt'.

    Thanks,

  • January 10, 2007 at 8:23 am

    #682391

    You can't use the aliased name withing the same query where it was produced (unless in the order by clause).  You either have to reuse the calculation in the next column or use a derived table.

  • January 10, 2007 at 11:49 am

    #682509

    Thanks, I've inlcuded formulas directly in the queries

     

     

     

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply