June 23, 2020 at 12:50 pm
ALTER FUNCTION [dbo].[Max1] (@Dem Dec(18,6), @UnitCost1 Dec(18,6), @AnnCost1 Dec(18,6), @EBQ1 Dec(18,6), @Min1 Dec(18,6) )
RETURNS Dec(8,0)
AS
BEGIN
DECLARE @Max2 Dec (8,0);
IF @UnitCost1 < 50 AND @AnnCost1<3000 THEN
SET @Max2=4*@Dem; -- 4 months of consumption
ELSEIF @UnitCost1 < 25 AND @AnnCost1<1500 THEN
SET @Max2=6*@Dem; -- 6 months of consumption
ELSE
SET @Max2=3*@Dem; -- 3 months of consumption
END IF;
IF @Max2-@Min1<@EBQ1 THEN SET @Max2 = @Min1 + @EBQ1 -- if max less than economical batch quantity adjusts max
RETURN @Max2
END
I get a bunch of errors;
Incorrect syntax near the keyword 'THEN'.
Msg 102, Level 15, State 1, Procedure Max1, Line 9 [Batch Start Line 2]
Incorrect syntax near 'ELSEIF'.
Msg 156, Level 15, State 1, Procedure Max1, Line 12 [Batch Start Line 2]
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure Max1, Line 15 [Batch Start Line 2]
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Procedure Max1, Line 17 [Batch Start Line 2]
Incorrect syntax near the keyword 'THEN'.
Msg 102, Level 15, State 1, Procedure Max1, Line 20 [Batch Start Line 2]
Incorrect syntax near 'END'.
Can anybody point me in the right direction. TIA.
June 23, 2020 at 1:18 pm
You are mixing some keywords from the SQL case statement with T-SQL. Try this instead:
if condition1
SET @Max2 = 1
else if condition2
SET @Max2 = 2
else
SET @Max2 = 3;
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 23, 2020 at 1:23 pm
it helps to read the manuals https://docs.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-ver15
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply