SQL Server - calling scalar functions in select query

Asked By muthuraman alexander on 29-Sep-11 06:13 AM
hi all
i hav a saclar function which ll return a varchar value
like this

ALTER FUNCTION [dbo].[fn_R_Current_MaxOcc](@RLCID INT,@Date VARCHAR(50))
RETURNS varchar(100)
AS
BEGIN
          DECLARE @ID int
          DECLARE @RptDate VarChar(50)
          DECLARE @R_C_MaxOcc   VarChar(50)

          SET @ID = @RLCID
          SET @RptDate = @Date
             
          SET @R_C_MaxOcc = (SELECT  TOP 1 AVG(R_Current) as R_C FROM view_select_RCurrent_MaxOccurance r
                                  WHERE rc_cnt=(select max(rc_cnt) from view_select_RCurrent_MaxOccurance as r1 WHERE r.RLCID=r1.RLCID)
                                           AND r.RLCID=@ID AND (convert(varchar(50),r.R_Date,101)=@RptDate)
                                  GROUP By r.RLCID,r.rc_cnt,r.R_Date
                                  ORDER BY RLCID)
                             

          RETURN @R_C_MaxOcc

END


----------------------------------------------------------------------------------------------------------------------------------

i wan to use this in select query
when i tried lik this
       select ID,name,date ,fn_R_Current_MaxOcc(ID,date ) as Current
it showing error
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_R_Current_MaxOcc", or the name is ambiguous.



--------------------------------------------------------

i need helpfor this

thanks in advance
MUTHU

Riley K replied to muthuraman alexander on 29-Sep-11 06:20 AM


Try calling the function with dbo object like this


select ID,name,date ,dbo.fn_R_Current_MaxOcc(ID,date ) as Current

Regards