0

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."

2
  • 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. Nov 12, 2013 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, 2013 at 6:48

1 Answer 1

2

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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