CODEWITHSUNDEEP
sqlssrs-2008user-defined-functions
Aaron Smith
Aaron Smith

Reputation: 151

UDF Table UDV or Scalar UDF?

I will do my best to make this question better than my last fiasco. I am getting the dreaded >"cannot find either column "dbo" or the user-defined function or aggregate "dbo.PriMonthAvgPrice", or the name is ambiguous.<

I am attempting to find the avg sales price from the previous month. Here is my UDF:

USE [WoodProduction]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[PriMonthAvgPrice]
(
-- Add the parameters for the function here
@endofmonth     datetime,
@begofmonth     datetime,
@PlantCode      varchar
)
RETURNS decimal (10,2)
AS
BEGIN
-- Declare the return variable here
DECLARE @MonthEndAvgPrice  decimal (10,2)

-- Add the T-SQL statements to compute the return value here
SELECT @MonthEndAvgPrice =

(
    select  

        sum(Actual_Sales_Dollars/Actual_Volume)

        FROM

        woodproduction.dbo.plywood_layup_sales pls
        WHERE

        Production_Date between @begofmonth and @endofmonth
        and actual_volume <> 0
        and @PlantCode = pls.Plant_Code


)

-- Return the result of the function
RETURN @MonthEndAvgPrice

END

This is my SELECT statement from my query:

    SELECT
DISTINCT    
    P.[Plant_Number]
    ,p.plant_name   
,pls.plant_code
    ,(pls.[Budget_Realization]) AS 'BR'
    ,(pls.[Actual_Volume] ) AS 'AV'
    ,(pls.[Budget_Volume])  AS 'BV'
--,sum (dpb.[Gross_Production_Per_Hr]) AS 'GPB'
    ,(p.Production_Volume) AS 'PV'
    ,CASE 
        WHEN    coalesce (pls.[Actual_Volume],0) = 0 and
                coalesce (pls.[Actual_Sales_Dollars],0) = 0
                THEN 0
        ELSE (pls.[Actual_Sales_Dollars]/pls.[Actual_Volume])
    END
    AS 'AP'
    ,pls.production_date
  ,[dbo].[PriMonthAvgPrice](@endofmonth,@begofmonth, pls.plant_code) AS 'PriMoAvgPrice'

My BASIC understanding is that I HAVE created a Scalar Function. From what I've been reading about my error however, This error returns on TVF's. Is this true? I created a SVF prior to this dealing with just determining a prior month end date so it wasn't as involved as this one where I create the query in the UDF.

Do I need to change this to a TVF? And if so, how do I incorporate SELECT * when I have to join multiple tables along with this?

Thanks in advance.

Aaron

Upvotes: 0

Views: 321

Answers (1)

HLGEM
HLGEM

Reputation: 96610

You don't show the from clause, but is the database you created the function in part of it?

Does it work if you fully qualify the name (include the database)?

Have you independently tested the function with:

select [dbo].[PriMonthAvgPrice] ('01/01/2011', '02/01/2011', 'test')

Note: of course you would use some actual values that should return a result.

Please run this and tell us the values returned:

 SELECT Actual_Sales_Dollars,Actual_Volume, pls.PLant_code          
 FROM   woodproduction.dbo.plywood_layup_sales pls         
 WHERE  Production_Date between '09-01-2011'  and  '09-30-2011'          
        and actual_volume <> 0

Upvotes: 1

Related Questions