UDF Table UDV or Scalar UDF?

Question

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

I'm a bit suspicious since I see a table reference qualified with a database name (woodproduction.dbo.plywood_layup_sales) in your function. Are you running this query in the same database where the function is created? — Joe Stefanelli
pls.[Actual_Sales_Dollars]/pls.[Actual_Volume]) = beware of integer math here if both your columns are integers. Also, I usually put ina a divide by zero check in any divsion, so that it won't brak if the bottom figure is 0 or null. You know you data and maybe it's not needed but it's a good habit to get into. Lots of times I have had an unexpected zero. — HLGEM
SELECT * should not be used in production code especially when there are joins. It is inefficient and causes the database to send more information across the newtork than is needed (the join fields are duplicated). — HLGEM
@HLGEM I figured as much and there are a LOT of joins throughout the whole query. I did a basic UDF for another dataset in this report but it didn't involve using a query like this. It was just a DATE UDF. — Aaron Smith

HLGEM HLGEM · Accepted Answer · 2011-10-20T18:10:50

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

UDF Table UDV or Scalar UDF?

1 Answers