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_IDENTIFI ER ON
go


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

/*
Use in conference attendance report

*/

(
Select top 100 percent * from ViewConfAttnRat e
where acyear=@acyear

UNION

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

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:

[code=sql]

set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
go


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

/*
Use in conference attendance report

*/

(
Select top 100 percent * from ViewConfAttnRat e
where acyear=@acyear

UNION

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

[/code]

[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_IDENTIFI ER ON
go


ALTER FUNCTION [dbo].[ConfAttnRptSmry]

(

@acyear smallint

)

RETURNS TABLE

AS

RETURN

/*

Use in conference attendance report

*/

(

SELECT top 100 percent * FROM ViewConfAttnRat e

WHERE acyear=@acyear

UNION

SELECT top 100 percent * FROM

(

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

CAST(acyear AS char(4))

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

@acyear acyear,rotation ,pgy,dbo.idtoTr ainPrg(eid) Program,w.srvco de,

schrecid,confty pe,0 a,0 b,0 c,0 d,0 e,shortname,hos p,abbrev

FROM schedule s, serviceward w,

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

WHERE acyear=@acyear

AND s.srvcode=w.srv code

) t )