By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,516 Members | 1,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,516 IT Pros & Developers. It's quick & easy.

Function - missing parentheses

P: 46
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.
Mar 12 '08 #1
Share this Question
Share on Google+
2 Replies


amitpatel66
Expert 100+
P: 2,367
Yes you are missing one:
Try This:

Expand|Select|Wrap|Line Numbers
  1.  
  2. set ANSI_NULLS ON
  3. set QUOTED_IDENTIFIER ON
  4. go
  5.  
  6.  
  7. ALTER FUNCTION [dbo].[ConfAttnRpt1] 
  8. @acyear smallint
  9. )
  10. RETURNS TABLE 
  11. AS
  12. RETURN 
  13.  
  14. /* 
  15. Use in conference attendance report
  16.  
  17. */
  18.  
  19. (
  20. Select top 100 percent * from ViewConfAttnRate
  21. where acyear=@acyear
  22.  
  23. UNION
  24.  
  25. SELECT top 100 percent * FROM 
  26. (
  27. select dbo.idtoname(eid) [Name],eid,
  28. CAST(acyear AS char(4))
  29. +CASE WHEN rotation>=10 THEN CAST(rotation AS char(2)) ELSE '0'+CAST(rotation AS char(1)) END [Period],
  30. @acyear acyear,rotation,pgy,dbo.idtoTrainPrg(eid) Program,w.srvcode,schrecid,
  31. conftype,0 a,0 b,0 c,0 d,0 e,shortname,hosp,abbrev
  32. from schedule s, serviceward w, (select distinct conftype from confattnsrvrate where acyear=@acyear) c
  33. where acyear=@acyear 
  34. and s.srvcode=w.srvcode
  35. ))
  36.  
  37.  
Mar 12 '08 #2

P: 46
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 )
Mar 14 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.