Post reply

Reusing calculated columns

  • September 13, 2005 at 5:51 am

    #187582

    I am quite new to this so forgive me if this is a simple question.

    I am currently converting a database from access to sql server, in rewriting one of the queries I wish to use a calculated column again, is this at all possible.

    For example

    select [Due Date] - [+31b] as prodweekend, fn_somefunction(prodweekend) from [order details]

    It will say prodweekend is an invalid column, is there anyway at all around this?

     

  • September 13, 2005 at 6:11 am

    #589314

    Robert - your prodweekend is an alias...use the actual column names in your function - something like this...

    select [Due Date] - [+31b] as prodweekend, fn_somefunction([Due Date] - [+31b]) as calcColumn from [order details]







    **ASCII stupid question, get a stupid ANSI !!!**

  • September 13, 2005 at 9:22 am

    #589412

    Or, to avoid replicating the expression (which is a potential cause of code maintenance issues down the road), use a derived table ...

    SELECT prodweekend, fn_somefunction(prodweekend) as calccolumn

    FROM

    (

      SELECT [Due Date] - [+31b] as prodweekend

      FROM [order details]

    ) dt

     

  • September 13, 2005 at 10:20 am

    #589440

    I have a string of 12 columns, each derived using the expression calculated in the last. Decided a trigger on insert will be the best way forward????

  • September 13, 2005 at 10:41 am

    #589450

    Is there some reason not to use actual computed columns, instead of calculating in the select statement?

  • September 13, 2005 at 10:52 am

    #589452

    Or use an SP instead of a trigger

     


    * Noel

  • September 14, 2005 at 6:53 am

    #589674

    Can't use computed columns as you can compute one column but then you can't derive another computed column from another computed column.

    Wrote a trigger which if it worked would be excellent but it doesn't quite. (It is posted in the post titled trigger help)

    If I were to use a stored procedure would I have to use a cursor to traverse my table?

  • September 14, 2005 at 6:54 am

    #589675

    Can't use computed columns as you can compute one column but then you can't derive another computed column from another computed column.

    Wrote a trigger which if it worked would be excellent but it doesn't quite. (It is posted in the post titled trigger help)

    If I were to use a stored procedure would I have to use a cursor to traverse my table?

  • September 14, 2005 at 7:05 am

    #589685

    You can always do this

    alter table a

    add cc1 as a * b

    cc2 as a * b + c

    ....

    Assuming that the formulas aren't too complex and that you won't have to update them down the road it shouldn't be a problem.

Viewing 9 posts - 1 through 8 (of 8 total)

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