FUNCTION with CASE statement

  • Thread starter Thread starter Hansjörg Zimmermann
  • Start date Start date
H

Hansjörg Zimmermann

Hi,

i try to implement a function which contains a CASE statement. Unfortunatly
i did only found samples with CASE statements within SQL-Statements.

The following code was rejected by the SQL-Server, and i have no idea what
the error is. Any ideas are welcome.
Thanks in advance.

--------------------------------------------------
CREATE FUNCTION [CalcFuelIMP] (@pVal integer, @pUnit tinyint, @pEngine
Char(1) )
RETURNS NUMERIC(12,5) AS

BEGIN

DECLARE @pResult Numeric(12,5)

SELECT CASE
WHEN (@pVal = 0 THEN SET @pResult = 0
WHEN @pUnit= 1 THEN SET @pResult = (@pVal)
WHEN @pUnit= 2 THEN SET @pResult = (@pVal / 4.543 * 3.7853)
WHEN @pUnit= 3 THEN SET @pResult = (@pVal * 4.543)
WHEN (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 4 THEN SET @pResult =
(@pVal * 0.4535924 / 0.8 / 4.543)
WHEN (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 5 THEN SET @pResult =
(@pVal / 0.8 / 3.7853)
WHEN (@pEngine = 'P' and @pUnit = 4 THEN SET @pResult = (@pVal * 0.4535924 /
0.719 / 4.543)
WHEN (@pEngine = 'P' and @pUnit = 5 THEN SET @pResult = (@pVal / 0.719 /
3.7853)
ELSE SET @pResult = -1

RETURN @pResult

END

GO
 
Hi Hansjoerg ,

The CASE statement is a Function
----
SET @pResult=CASE @pUnit WHEN 1 THEN @pVal
WHEN 2 THEN @pVal / 4.543 * 3.7853
WHEN 3 THEN .......
......
......
ELSE -1 END
----

in your function I would use the IF statement
----
CREATE FUNCTION [CalcFuelIMP] (@pVal integer, @pUnit tinyint, @pEngine
Char(1) )
RETURNS NUMERIC(12,5) AS

BEGIN

DECLARE @pResult NUMERIC(12,5)

IF @pVal = 0 BEGIN
SET @pResult = 0
GOTO End_CalcFuelIMP
END

IF @pUnit= 1 BEGIN
SET @pResult = (@pVal)
GOTO End_CalcFuelIMP
END

IF @pUnit= 2 BEGIN
SET @pResult = (@pVal / 4.543 * 3.7853)
GOTO End_CalcFuelIMP
END

IF @pUnit= 3 BEGIN
SET @pResult = (@pVal * 4.543)
GOTO End_CalcFuelIMP
END

IF (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 4 BEGIN
SET @pResult = (@pVal * 0.4535924 / 0.8 / 4.543)
GOTO End_CalcFuelIMP
END

IF (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 5 BEGIN
SET @pResult = (@pVal / 0.8 / 3.7853)
GOTO End_CalcFuelIMP
END

IF (@pEngine = 'P' and @pUnit = 4 ) BEGIN
SET @pResult = (@pVal * 0.4535924 / 0.719 / 4.543)
GOTO End_CalcFuelIMP
END

IF (@pEngine = 'P' and @pUnit = 5 )
SET @pResult = (@pVal / 0.719 / 3.7853)
ELSE
SET @pResult = -1

End_CalcFuelIMP:

RETURN @pResult

END
 
If you want to use the CASE statement
----
ALTER FUNCTION [CalcFuelIMP] (@pVal integer, @pUnit tinyint, @pEngine
Char(1) )
RETURNS NUMERIC(12,5) AS

BEGIN

RETURN CASE WHEN @pVal = 0 THEN 0
WHEN @pUnit= 1 THEN @pVal
WHEN @pUnit= 2 THEN (@pVal / 4.543 * 3.7853)
WHEN @pUnit= 3 THEN (@pVal * 4.543)
WHEN (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 4 THEN
(@pVal * 0.4535924 / 0.8 / 4.543)
WHEN (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 5 THEN
(@pVal / 0.8 / 3.7853)
WHEN (@pEngine = 'P' and @pUnit = 4) THEN (@pVal * 0.4535924 /
0.719 / 4.543)
WHEN (@pEngine = 'P' and @pUnit = 5) THEN (@pVal / 0.719 /
3.7853)
ELSE -1 END

END
----

Bye
Giorgio

giorgio rancati said:
Hi Hansjoerg ,

The CASE statement is a Function
----
SET @pResult=CASE @pUnit WHEN 1 THEN @pVal
WHEN 2 THEN @pVal / 4.543 * 3.7853
WHEN 3 THEN .......
......
......
ELSE -1 END
----

in your function I would use the IF statement
----
CREATE FUNCTION [CalcFuelIMP] (@pVal integer, @pUnit tinyint, @pEngine
Char(1) )
RETURNS NUMERIC(12,5) AS

BEGIN

DECLARE @pResult NUMERIC(12,5)

IF @pVal = 0 BEGIN
SET @pResult = 0
GOTO End_CalcFuelIMP
END

IF @pUnit= 1 BEGIN
SET @pResult = (@pVal)
GOTO End_CalcFuelIMP
END

IF @pUnit= 2 BEGIN
SET @pResult = (@pVal / 4.543 * 3.7853)
GOTO End_CalcFuelIMP
END

IF @pUnit= 3 BEGIN
SET @pResult = (@pVal * 4.543)
GOTO End_CalcFuelIMP
END

IF (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 4 BEGIN
SET @pResult = (@pVal * 0.4535924 / 0.8 / 4.543)
GOTO End_CalcFuelIMP
END

IF (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 5 BEGIN
SET @pResult = (@pVal / 0.8 / 3.7853)
GOTO End_CalcFuelIMP
END

IF (@pEngine = 'P' and @pUnit = 4 ) BEGIN
SET @pResult = (@pVal * 0.4535924 / 0.719 / 4.543)
GOTO End_CalcFuelIMP
END

IF (@pEngine = 'P' and @pUnit = 5 )
SET @pResult = (@pVal / 0.719 / 3.7853)
ELSE
SET @pResult = -1

End_CalcFuelIMP:

RETURN @pResult

END
----

Bye
Giorgio

Hansjörg Zimmermann said:
Hi,

i try to implement a function which contains a CASE statement. Unfortunatly
i did only found samples with CASE statements within SQL-Statements.

The following code was rejected by the SQL-Server, and i have no idea what
the error is. Any ideas are welcome.
Thanks in advance.

--------------------------------------------------
CREATE FUNCTION [CalcFuelIMP] (@pVal integer, @pUnit tinyint, @pEngine
Char(1) )
RETURNS NUMERIC(12,5) AS

BEGIN

DECLARE @pResult Numeric(12,5)

SELECT CASE
WHEN (@pVal = 0 THEN SET @pResult = 0
WHEN @pUnit= 1 THEN SET @pResult = (@pVal)
WHEN @pUnit= 2 THEN SET @pResult = (@pVal / 4.543 * 3.7853)
WHEN @pUnit= 3 THEN SET @pResult = (@pVal * 4.543)
WHEN (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 4 THEN SET @pResult =
(@pVal * 0.4535924 / 0.8 / 4.543)
WHEN (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 5 THEN SET @pResult =
(@pVal / 0.8 / 3.7853)
WHEN (@pEngine = 'P' and @pUnit = 4 THEN SET @pResult = (@pVal *
Click to expand...
0.4535924
/
0.719 / 4.543)
WHEN (@pEngine = 'P' and @pUnit = 5 THEN SET @pResult = (@pVal / 0.719 /
3.7853)
ELSE SET @pResult = -1

RETURN @pResult

END

GO
Click to expand...
Click to expand...
 
There are several places in your function where you have open parentheses,
but no close parentheses...could that be the problem?


Rob
 
Tanks a lot for your hints. I guess it was to late at night, when i have
written this function.

The problem was of corse also the parentheses, but the basic problem
was that i have used the CASE statement in a wrong way as explained
in the answers of Giorgio.

Brgds

Hansjörg

Robert Morley said:
There are several places in your function where you have open parentheses,
but no close parentheses...could that be the problem?


Rob

Hansjörg Zimmermann said:
Hi,

i try to implement a function which contains a CASE statement.
Unfortunatly i did only found samples with CASE statements within
SQL-Statements.

The following code was rejected by the SQL-Server, and i have no idea
what the error is. Any ideas are welcome.
Thanks in advance.

--------------------------------------------------
CREATE FUNCTION [CalcFuelIMP] (@pVal integer, @pUnit tinyint, @pEngine
Char(1) )
RETURNS NUMERIC(12,5) AS

BEGIN

DECLARE @pResult Numeric(12,5)

SELECT CASE
WHEN (@pVal = 0 THEN SET @pResult = 0
WHEN @pUnit= 1 THEN SET @pResult = (@pVal)
WHEN @pUnit= 2 THEN SET @pResult = (@pVal / 4.543 * 3.7853)
WHEN @pUnit= 3 THEN SET @pResult = (@pVal * 4.543)
WHEN (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 4 THEN SET @pResult
= (@pVal * 0.4535924 / 0.8 / 4.543)
WHEN (@pEngine = 'J' or @pEngine = 'T') and @pUnit = 5 THEN SET @pResult
= (@pVal / 0.8 / 3.7853)
WHEN (@pEngine = 'P' and @pUnit = 4 THEN SET @pResult = (@pVal *
0.4535924 / 0.719 / 4.543)
WHEN (@pEngine = 'P' and @pUnit = 5 THEN SET @pResult = (@pVal / 0.719 /
3.7853)
ELSE SET @pResult = -1

RETURN @pResult

END

GO
Click to expand...
Click to expand...
 
Back
Top