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!
AngiHi 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> ¼¶¼g©ó¶l¥ó·s»D
: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...
> > 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
> >
> >
>
Showing posts with label fnxx04. Show all posts
Showing posts with label fnxx04. Show all posts
Friday, February 24, 2012
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...
>
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...
>
Subscribe to:
Posts (Atom)