calculating created column in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

calculating created column in stored procedure

Author

Topic

bfundy01
Starting Member

2 Posts

Posted - 2010-02-03 : 21:37:12

I'm creating a stored procedure where one of the columns is created by the procedure itself. I then need to take that column and do a calculation on it to derive a result. More specifically, here is what my table looks like:
amount, discount
10, .5
20, .3

In my procedure, I add another column from another table, called salePrice.

I then need to take these columns and calulate them so the result is something like this.
amount, discount, salePrice, total
10, .5, 3.00, 25
20, .3, 3.00, 54

This is all working fine except the total column. Here is the basic procedure.

SELECT a.amount, a.discount, salePrice = Case
--<not including case statement for clarity>
END, (select (salePrice - discount) * amount) 'total'
FROM table1 A LEFT JOIN table2 B ON -- <etc>

When I try to reference salePrice (3rd line), I get an invalid column name error. How does this need to be referenced so it can be used by the calculation?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-03 : 21:43:22

you can do this in a derived table but not directly as what you have done


select amount, discount, salesPrice, (salesprice - discount) * amount 'total'
from
(
    SELECT a.amount, a.discount, salePrice = Case
    --<not including case statement for clarity>
    END, (select (price - salePrice) * amount) 'total'
    price
    FROM table1 A LEFT JOIN table2 B ON -- <etc>
) as D

KH
[spoiler]Time is always against us[/spoiler]

bfundy01
Starting Member

2 Posts

Posted - 2010-02-03 : 22:06:45

Sorry, updated post as "price" is a typo and should have said "discount" on the third line. this works nicely.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources