FUNCTION with CASE statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hansjörg Zimmermann - 25 Sep 2005 19:29 GMT

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
--------------------------------------------------

Chers
Hansjoerg Zimmermann

Reply to this Message

giorgio rancati - 25 Sep 2005 21:09 GMT

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

> Hi,
>
[quoted text clipped - 38 lines]
> Chers
> Hansjoerg Zimmermann

Reply to this Message

giorgio rancati - 25 Sep 2005 21:28 GMT

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

> Hi Hansjoerg ,
>
[quoted text clipped - 112 lines]
> > Chers
> > Hansjoerg Zimmermann

Reply to this Message

Robert Morley - 26 Sep 2005 15:45 GMT

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

Rob

> Hi,
>
[quoted text clipped - 39 lines]
> Chers
> Hansjoerg Zimmermann

Reply to this Message

Hansjörg Zimmermann - 26 Sep 2005 19:40 GMT

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

> There are several places in your function where you have open parentheses,
> but no close parentheses...could that be the problem?
[quoted text clipped - 44 lines]
>> Chers
>> Hansjoerg Zimmermann

Reply to this Message