Calculated Field - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Calculated Field

Author

Topic

Volkof
Starting Member

14 Posts

Posted - 2014-11-18 : 15:09:06

Hello
i have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?

this query returns the values i want in the table.

select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 15:17:49

http://stackoverflow.com/questions/2769007/formula-for-computed-column-based-on-different-tables-column

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Volkof
Starting Member

14 Posts

Posted - 2014-11-18 : 16:51:20

based on that example i came up with this:

create function fc.AmountSpent(@ProductKey as Int, @Quantity as Int)
returns int
as
select @Quantity * Listprice
from tblProduct
where ProductKey = @ProductKey

I get an error, 'select' is underlined ("Incorrect syntax near select. Expecting BEgin_cs, External, or Returned.")

Please help. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-18 : 17:18:08

What tool are you using? That error doesn't look like it would be thrown from Management Studio.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Volkof
Starting Member

14 Posts

Posted - 2014-11-19 : 03:15:59

Hy
The error is just :"Incorrect syntax near select. Expecting BEgin_cs, External, or Returned."
table Product
<ProductKey> <Price>
table Order
<CustomerKey><Quantity> (and i want to add <AmountSpent>)
AmountSpent = Price * Quantity

I'm using SQL Server Management Studio 2012 Express Edition

Thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 09:13:14

Here is your function with the correct syntax:


CREATE FUNCTION fc.AmountSpent(@ProductKey AS int, 
                               @Quantity AS int)
RETURNS int
AS
BEGIN
    DECLARE @val money;
    SET @val = (SELECT @Quantity * Listprice
                  FROM tblProduct
                  WHERE ProductKey = @ProductKey);
    RETURN @val;
END;

Volkof
Starting Member

14 Posts

Posted - 2014-11-19 : 17:42:55

Thanks gbritton, the function MoneySpent was created successfully.

I have an error msg. now with this statement:
alter table tblcustomerPurchases
add AmountSpent as MoneySpent(1,1)

Msg 195, Level 15, State 10, Line 3
'MoneySpent' is not a recognized built-in function name

What should I insert as values for @ProductKey and @Quantity?

Thanks a lot!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-19 : 19:46:24

You need to specify the schema when referencing functions, such as dbo.FunctionName.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-19 : 19:48:05

Regarding what to pass the function, you would pass it the columns from the Order table.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Volkof
Starting Member

14 Posts

Posted - 2014-11-21 : 02:30:08

Ok ,thanks.
But, how do I do that? :)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-21 : 09:09:26

You do it in the CREATE TABLE statement. e.g.


CREATE TABLE mytable(
    col1 int, col2 int, 
    computed_column as dbo.compute_colum(col1, col2)
)

Volkof
Starting Member

14 Posts

Posted - 2014-11-24 : 05:32:20

Thank you.
alter table tblCustomerPurchases
add MoneySpent as dbo.MoneySpent(Productkey,QuantityPurchased)

this statement did the job but i have a formatting problem now. It gives me the whole number. Ex. 18 instead of 18.45.
I've tried to change the function :datatype ( DECLARE @val as INT ) or RETURN FORMAT(@val,'N'); but the give me this error when select * from tblCustomerPurchases:
'Conversion failed when converting the nvarchar value '18.45' to data type int'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 07:04:47

SQL won't auto covert nvarchar to int. Which value that you pass to the function is nvarchasr?

Volkof
Starting Member

14 Posts

Posted - 2014-11-24 : 15:24:04

None are nvarchar.

ProductKey int
ListPrice (in the Product table) money
QuantityPurchased int

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-24 : 15:31:26

Your function is outputting an int, which does not have decimal places. Change it to money or decimal.

It's this part specifically: RETURNS int

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 15:32:26

You said you changed this RETURN FORMAT(@val,'N'). That's where nvarchar came in

Note that the function only uses integers. you'll need to change that to money. Also the function takes in ProductKey, shouldn't that be QuantityPurchased? Just sayin! Money spent should be quantity * price, usually.

Volkof
Starting Member

14 Posts

Posted - 2014-11-25 : 15:59:32

When it's about a nvarchar datatype i had this error"Conversion failed when converting the nvarchar value '18.45' to data type int"
When the columns are int i got the whole number.

In the end i changed all my variables to money and it worked. Many thanks for your help.

ALTER function [dbo].[MoneySpent](@ProductKEy as money,@Quantity as money)
returns money
As
BEgin
Declare @val as money;
Set @val = (Select CAST(@Quantity as money) * CAST(ListPrice as money)
From tblProduct where ProductKey = @ProductKEy);
Return @val
end;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-26 : 09:38:06

This is still wrong (even though it works):


ALTER function [dbo].[MoneySpent](@ProductKEy as money,@Quantity as money)

Since ProductKey is an int, that should be:


ALTER function [dbo].[MoneySpent](@ProductKEy as int,@Quantity as money)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources