Error - The multi-part identifier "dbo.ComputeLowest" could not be bound.

• jsshivalik

  SSChasing Mays

  Points: 612

  More actions

  October 26, 2018 at 5:00 am

  #381854

  Hi

    I have written below code in function . When i execute 

  select [dbo].[ComputeLowest] it gives above error

  ALTER FUNCTION [dbo].[ComputeLowest] ()RETURNS DECIMAL(18,2)
  BEGIN
  DECLARE @ActualPercentage DEC (18,2) , @Budgeted DEC (18,2), @Achieved DEC (18,2)  SELECT @Budgeted = SUM (Target) FROM [test] SELECT @Achieved = SUM (Achievement) FROM [test]
  SET @ActualPercentage = @Budgeted - @Achieved
  RETURN @ActualPercentage
  End

  Thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  October 26, 2018 at 7:17 am

  #2011333

  jsshivalik - Friday, October 26, 2018 5:00 AM

  Hi

    I have written below code in function . When i execute 

  select [dbo].[ComputeLowest] it gives above error

  ALTER FUNCTION [dbo].[ComputeLowest] ()RETURNS DECIMAL(18,2)
  BEGIN
  DECLARE @ActualPercentage DEC (18,2) , @Budgeted DEC (18,2), @Achieved DEC (18,2)  SELECT @Budgeted = SUM (Target) FROM [test] SELECT @Achieved = SUM (Achievement) FROM [test]
  SET @ActualPercentage = @Budgeted - @Achieved
  RETURN @ActualPercentage
  End

  Thanks

  You'll need the parenthesis in the query
  select dbo].ComputeLowest()
  Have you checked if the table [dbo].[test] and the function [dbo].[ComputeLowest] exists in the current database?
  😎

  Further, I suggest you convert the scalar function into a inline table valued function, something like this
  
CREATE OR ALTER FUNCTION [dbo].[ComputeLowest] ()
RETURNS TABLE
AS
RETURN
WITH Budgeted(VAL) AS
(
SELECT
  SUM (T.Target) AS VAL
FROM dbo.test T
)
,Achieved(VAL) AS
(
SELECT
  SUM (T.Achievement) AS VAL
FROM dbo.test T
)
SELECT
  (B.VAL - A.VAL) AS ActualPercentage
FROM   Budgeted  B
CROSS APPLY Achieved  A
;