Function - missing parentheses

[teneesh]

I'm trying to create a function and the error I keep receiving is that I am missing a ')' in line 34. Line 34 in SQL Server is in bold below:

from schedule s, serviceward w, (select distinct conftype from confattnsrvrate where acyear=@acyear) c

Here is all the code for the function.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER FUNCTION [dbo].[ConfAttnRpt1]
(
@acyear smallint
)
RETURNS TABLE
AS
RETURN

/*
Use in conference attendance report

*/

(
Select top 100 percent * from ViewConfAttnRate
where acyear=@acyear

UNION

SELECT top 100 percent * FROM
(
select dbo.idtoname(eid) [Name],eid,
CAST(acyear AS char(4))
+CASE WHEN rotation>=10 THEN CAST(rotation AS char(2)) ELSE '0'+CAST(rotation AS char(1)) END [Period],
@acyear acyear,rotation,pgy,dbo.idtoTrainPrg(eid) Program,w.srvcode,schrecid,
conftype,0 a,0 b,0 c,0 d,0 e,shortname,hosp,abbrev
from schedule s, serviceward w, (select distinct conftype from confattnsrvrate where acyear=@acyear) c
where acyear=@acyear
and s.srvcode=w.srvcode
)

Please tell me how to fix this because it seems to me I'm not missing another ')'
Thank you.

[amitpatel66]

Yes you are missing one:
Try This:

 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
ALTER FUNCTION [dbo].[ConfAttnRpt1] 
( 
@acyear smallint
)
RETURNS TABLE 
AS
RETURN 
 
/* 
Use in conference attendance report
 
*/
 
(
Select top 100 percent * from ViewConfAttnRate
where acyear=@acyear
 
UNION
 
SELECT top 100 percent * FROM 
(
select dbo.idtoname(eid) [Name],eid,
CAST(acyear AS char(4))
+CASE WHEN rotation>=10 THEN CAST(rotation AS char(2)) ELSE '0'+CAST(rotation AS char(1)) END [Period],
@acyear acyear,rotation,pgy,dbo.idtoTrainPrg(eid) Program,w.srvcode,schrecid,
conftype,0 a,0 b,0 c,0 d,0 e,shortname,hosp,abbrev
from schedule s, serviceward w, (select distinct conftype from confattnsrvrate where acyear=@acyear) c
where acyear=@acyear 
and s.srvcode=w.srvcode
))
 
 

[teneesh]

Actually - I figured it out!!! I had to name the last set of parentheses. So after putting t after the second group of parentheses, it worked. Here it is:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER FUNCTION [dbo].[ConfAttnRptSmry]

(

@acyear smallint

)

RETURNS TABLE

AS

RETURN

/*

Use in conference attendance report

*/

(

SELECT top 100 percent * FROM ViewConfAttnRate

WHERE acyear=@acyear

UNION

SELECT top 100 percent * FROM

(

SELECT dbo.idtoname(eid) [Name],eid,

CAST(acyear AS char(4))

+CASE WHEN rotation>=10 THEN CAST(rotation AS char(2)) ELSE '0'+CAST(rotation AS char(1)) END [Period],

@acyear acyear,rotation,pgy,dbo.idtoTrainPrg(eid) Program,w.srvcode,

schrecid,conftype,0 a,0 b,0 c,0 d,0 e,shortname,hosp,abbrev

FROM schedule s, serviceward w,

(SELECT DISTINCT conftype FROM confattnsrvrate WHERE acyear=@acyear) c

WHERE acyear=@acyear

AND s.srvcode=w.srvcode

) t )