How to use CASE WHEN statement in Function

Hi,
My Function as follow-->
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
IF @.cemk = '1'
BEGIN
RETURN 'XX0400100'
END
RETURN 'XX0400200'
END
My Case When statement like this-->
CASE
WHEN iv_cemk = 0 THEN 'XX0400200'
ELSE 'XX0400100'
END AS iv_cemk
And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
Function?
How should I do?
Thanks!
Angi
Hi Angi,
what about
CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
RETURNS CHAR(9)
AS
BEGIN
declare @.cReturn char(9)
set @.cReturn = (case @.cemk
when '1' then 'XX0400100'
else 'XX0400200'
end)
return @.cReturn
END
HTH
Meinhard
"angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
> Hi,
> My Function as follow-->
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> IF @.cemk = '1'
> BEGIN
> RETURN 'XX0400100'
> END
> RETURN 'XX0400200'
> END
> My Case When statement like this-->
> CASE
> WHEN iv_cemk = 0 THEN 'XX0400200'
> ELSE 'XX0400100'
> END AS iv_cemk
> And could I replace IF @.cmk='1' BEGIN... to CASE WHEN statement in
> Function?
> How should I do?
> Thanks!
> Angi
>
|||Hi, Meinhard
Thank you very much!
And there is aother way like follow..
BEGIN
RETURN CASE WHEN @.cemk='1' THEN 'XX0400100'
ELSE 'XX0400200'
END
END
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
And I have another question there, syntax like follow..
CREATE FUNCTION fnXY13 (@.iden CHAR(4), @.csct CHAR(2), @.cect CHAR(2))
RETURNS CHAR(9)
AS
BEGIN
RETURN
CASE--XY13AA
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.csct = 'f1' THEN 'XY1300100'
WHEN @.csct = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13BB
WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
CASE--XY13CC
WHEN @.iden IN ('0406','0408','0409','0419','0501') THEN
CASE
WHEN @.cect = 'f1' THEN 'XY1300100'
WHEN @.cect = 'f2' THEN 'XY1300200'
ELSE 'XY1300500'
END
ELSE 'X99999999'
END
END
There are 3 different CASE WHEN conditions and how could I combine it in a
Function?
Thanks!
Angi
"Meinhard Schnoor-Matriciani" <codehack@.freenet.de> glsD
:2s4ev1F1h4vb5U1@.uni-berlin.de...
> Hi Angi,
> what about
>
> CREATE FUNCTION fnXX04 ( @.cemk CHAR(1))
> RETURNS CHAR(9)
> AS
> BEGIN
> declare @.cReturn char(9)
> set @.cReturn = (case @.cemk
> when '1' then 'XX0400100'
> else 'XX0400200'
> end)
> return @.cReturn
> END
> HTH
> Meinhard
> "angi" <angi@.microsoft.public.sqlserver.olap> schrieb im Newsbeitrag
> news:OJrAXO4pEHA.2864@.TK2MSFTNGP12.phx.gbl...
>
|||Same as the answer in the previous post. Just assign to variables in your
logic, then RETURN the variable at the end.
Get the CASE statements working stand-alone in QA first.
Jeff
"angi" <angi@.microsoft.public.sqlserver.olap> wrote in message
news:eqMxG95pEHA.2484@.TK2MSFTNGP09.phx.gbl...
> Hi, Meinhard
> Thank you very much!
> And there is aother way like follow..
> BEGIN
> RETURN CASE WHEN @.cemk='1' THEN 'XX0400100'
> ELSE 'XX0400200'
> END
> END
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> And I have another question there, syntax like follow..
> CREATE FUNCTION fnXY13 (@.iden CHAR(4), @.csct CHAR(2), @.cect CHAR(2))
> RETURNS CHAR(9)
> AS
> BEGIN
> RETURN
> CASE--XY13AA
> WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
> CASE
> WHEN @.csct = 'f1' THEN 'XY1300100'
> WHEN @.csct = 'f2' THEN 'XY1300200'
> ELSE 'XY1300500'
> END
> ELSE 'X99999999'
> END
> CASE--XY13BB
> WHEN @.iden = '7660' AND @.iden LIKE '00%%' THEN
> CASE
> WHEN @.cect = 'f1' THEN 'XY1300100'
> WHEN @.cect = 'f2' THEN 'XY1300200'
> ELSE 'XY1300500'
> END
> ELSE 'X99999999'
> END
> CASE--XY13CC
> WHEN @.iden IN ('0406','0408','0409','0419','0501') THEN
> CASE
> WHEN @.cect = 'f1' THEN 'XY1300100'
> WHEN @.cect = 'f2' THEN 'XY1300200'
> ELSE 'XY1300500'
> END
> ELSE 'X99999999'
> END
> END
> There are 3 different CASE WHEN conditions and how could I combine it in a
> Function?
> Thanks!
> Angi
>
> "Meinhard Schnoor-Matriciani" <codehack@.freenet.de> glsD
> :2s4ev1F1h4vb5U1@.uni-berlin.de...
>