September 28, 2009 at 4:43 pm
When I try the following I get this invalid syntax error
Msg 156, Level 15, State 1, Procedure udf_OHBudgetsByPeriod_TAB, Line 32
Incorrect syntax near the keyword 'BEGIN'.
I can't see what I'm doing wrong. Could someone please educate me. Thanks
CREATE FUNCTION dbo.udf_OHBudgetsByPeriod_TAB (
@Library char(6) -- PRISM Library (database)
, @FinYr char(4) -- Financial Year
) RETURNS TABLE (
Job_Number char (6)--
, WBS_Number char(5)--
, Cost_Code char(4) --
, Cost_Year char(4)
, Period tinyint--
, Budget decimal (18,4)
)
/*
* description goes here
*
* Related Functions:
* Attribution: Based on xxx by yyy found in zzzzzzzzzzzzz
* Maintenace Notes:
* Example:
SELECT * FROM dbo.udf_OHBudgetsByPeriod_TAB(parfls, 2009 )
* Test Script: TEST_udf_OHBudgetsByPeriod_TAB
* History:
* When Who Description
* ------------- ------- -----------------------------------------
* 2009-09-29 DLG Initial Coding
****************************************************************/
AS
BEGIN
--DECLARE
IF @Library = 'PARFLS'
--BEGIN
SELECT Job_Number, WBS_Number, Cost_Code,Cost_Year,Period, Budget
FROM
(SELECT Job_Number, WBS_Number, Cost_Code, Cost_Year
, SUM(Revised_Amount_Period_01) AS "1"
, SUM(Revised_Amount_Period_02) AS "2"
, SUM(Revised_Amount_Period_03) AS "3"
, SUM(Revised_Amount_Period_04) AS "4"
, SUM(Revised_Amount_Period_05) AS "5"
, SUM(Revised_Amount_Period_06) AS "6"
, SUM(Revised_Amount_Period_07) AS "7"
, SUM(Revised_Amount_Period_08) AS "8"
, SUM(Revised_Amount_Period_09) AS "9"
, SUM(Revised_Amount_Period_10) AS "10"
, SUM(Revised_Amount_Period_11) AS "11"
, SUM(Revised_Amount_Period_12) AS "12"
FROM PARFLS.dbo.JobCostBudgetMaster AS BM
WHERE Cost_Year = @FinYr
GROUP BY Job_Number, WBS_Number, Cost_Code, Cost_Year) AS p
UNPIVOT (Budget for Period IN ("1","2", "3", "4", "5", "6", "7", "8","9","10","11", "12")) as unpiv
--RETURN
--END
IF @Library = 'MWCFLS'
--BEGIN
SELECT Job_Number, WBS_Number, Cost_Code,Cost_Year,Period, Budget
FROM
(SELECT Job_Number, WBS_Number, Cost_Code, Cost_Year
, SUM(Revised_Amount_Period_01) AS "1"
, SUM(Revised_Amount_Period_02) AS "2"
, SUM(Revised_Amount_Period_03) AS "3"
, SUM(Revised_Amount_Period_04) AS "4"
, SUM(Revised_Amount_Period_05) AS "5"
, SUM(Revised_Amount_Period_06) AS "6"
, SUM(Revised_Amount_Period_07) AS "7"
, SUM(Revised_Amount_Period_08) AS "8"
, SUM(Revised_Amount_Period_09) AS "9"
, SUM(Revised_Amount_Period_10) AS "10"
, SUM(Revised_Amount_Period_11) AS "11"
, SUM(Revised_Amount_Period_12) AS "12"
FROM MWCFLS.dbo.JobCostBudgetMaster AS BM
WHERE Cost_Year = @FinYr
GROUP BY Job_Number, WBS_Number, Cost_Code, Cost_Year) AS p
UNPIVOT (Budget for Period IN ("1","2", "3", "4", "5", "6", "7", "8","9","10","11", "12")) as unpiv
RETURN
--END
END
GO
GRANT SELECT ON [dbo].[udf_OHBudgetsByPeriod_TAB] TO [PUBLIC]
GO
September 28, 2009 at 5:50 pm
Here is your problem:
) RETURNS TABLE (
It needs to include a variable name:
) RETURNS @ReturnValue TABLE (
You need to insert the records into that variable..
I can't remember if you need to say
RETURN @ReturnValue
Or if just the return is ok..
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy