Multiple return values in CASE statement?

Hi,

I've got a short question, hoping someone can point me to the right
direction.
I have a rather complex SELECT statement with some CASE-query like

/-----
SELECT CASE
WHEN complexcalculations1 THEN returnvalue1
WHEN complexcalculations2 THEN returnvalue2
ELSE returnvalue3
END AS returnname
\-----

It works fine, but I want to have two different returnvalues for
whichever case is true.
I tried:

/-----
SELECT CASE
WHEN complexcalculations1 THEN returnvalue1a AS returnname_a,
returnvalue1b AS returnname_b
WHEN complexcalculations2 THEN returnvalue2a AS returnname_a,
returnvalue2b AS returnname_b
ELSE returnvalue3a AS returnname_a, returnvalue3b AS returnname_b
END
\-----

but that doesnt work. Of course I could use

/-----
SELECT CASE
WHEN complexcalculations1 THEN returnvalue1a
WHEN complexcalculations2 THEN returnvalue2a
ELSE returnvalue3a
END AS returnname_a
CASE
WHEN complexcalculations1 THEN returnvalue1b
WHEN complexcalculations2 THEN returnvalue2b
ELSE returnvalue3b
END AS returnname_b
\-----

But then all the complex calculations have to be done twice, which I
would call overhead.
Thanks for any ideas!

I would repeat the expressions, as it's likely the db will optimize to
avoid duplicate calculations hopefully. You could store it in a string
variable and just build up your query.

You could also use a sub-query:

select case X when A then P when B then Q else R end case from (select
X, [...] as A, [...] as B from T)

On Jul 27, 5:06 pm, busnet <goo...@00l.de> wrote:
> Hi,
>
> I've got a short question, hoping someone can point me to the right
> direction.
> I have a rather complex SELECT statement with some CASE-query like
>
> /-----
> SELECT CASE
> WHEN complexcalculations1 THEN returnvalue1
> WHEN complexcalculations2 THEN returnvalue2
> ELSE returnvalue3
> END AS returnname
> \-----
>
> It works fine, but I want to have two different returnvalues for
> whichever case is true.
> I tried:
>
> /-----
> SELECT CASE
> WHEN complexcalculations1 THEN returnvalue1a AS returnname_a,
> returnvalue1b AS returnname_b
> WHEN complexcalculations2 THEN returnvalue2a AS returnname_a,
> returnvalue2b AS returnname_b
> ELSE returnvalue3a AS returnname_a, returnvalue3b AS returnname_b
> END
> \-----
>
> but that doesnt work. Of course I could use
>
> /-----
> SELECT CASE
> WHEN complexcalculations1 THEN returnvalue1a
> WHEN complexcalculations2 THEN returnvalue2a
> ELSE returnvalue3a
> END AS returnname_a
> CASE
> WHEN complexcalculations1 THEN returnvalue1b
> WHEN complexcalculations2 THEN returnvalue2b
> ELSE returnvalue3b
> END AS returnname_b
> \-----
>
> But then all the complex calculations have to be done twice, which I
> would call overhead.
> Thanks for any ideas!