Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i have created fnDiscountPrice() that references the item id so that it will find the discount price for one item now I'm needing to find the total price of all the products with that ID. so i have a function that receives the ItemID and then multiplys the discount price found in fnDiscountPrice() with the same ID by the quantity. Code shown below.

CREATE FUNCTION dbo.fnItemTotal 
    (@ItemID INT = 0)
    RETURNS Table

RETURN (SELECT ItemID, SUM(dbo.fnDiscountPrice(@ItemID) * Quantity) AS TimeTotal
        FROM OrderItems
        GROUP BY ItemID)

I keep getting an error of "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnDiscountPrice", or the name is ambiguous."

share|improve this question
    
Check if dbo.fnDiscountPrice a scalar valued function or table valued function. You are using it as if it is a scalar valued function but if you have created a table valued function (which is better) you need to change the way you use it. –  Mikael Eriksson Nov 12 '13 at 6:38
    
ok since i did go about creating it as a table how do i have to use it? –  Josh Banks Nov 12 '13 at 6:48
add comment

1 Answer

Assuming that the column name returned from dbo.fnDiscountPrice is DiscountPrice you could use it like this.

CREATE FUNCTION dbo.fnItemTotal 
    (@ItemID INT = 0)
    RETURNS Table

RETURN (SELECT O.ItemID, SUM(DP.DiscountPrice * O.Quantity) AS TimeTotal
        FROM dbo.fnDiscountPrice(@ItemID) as DP
          CROSS APPLY OrderItems as O
        GROUP BY O.ItemID)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.