Is there is a way in DB2 to return multiple fields from a single case

[lenygold via DBMonster.com]

Hi everybody!

I am truing to concstruct something like this:

SELECT id, name, role,
CASE
WHEN role = 1 THEN SELECT item1, item2, item3 FROM table1
WHEN role = 2 THEN SELECT item1, item2, item3 FROM table2
ELSE SELECT item1, item2, item3 FROM table3
END as THIS, THAT, THOSE
FROM roles_tbl
Is this is valid in any DB2 VERSIONS

--
Message posted via http://www.dbmonster.com

[Serge Rielau]

Re: Is there is a way in DB2 to return multiple fields from a singlecase

lenygold via DBMonster.com wrote:

Hi everybody!
>
I am truing to concstruct something like this:
>
SELECT id, name, role,
CASE
WHEN role = 1 THEN SELECT item1, item2, item3 FROM table1
WHEN role = 2 THEN SELECT item1, item2, item3 FROM table2
ELSE SELECT item1, item2, item3 FROM table3
END as THIS, THAT, THOSE
FROM roles_tbl

SELECT item1, item2, item3 FROM table1 WHERE role = 1
UNION ALL
SELECT item1, item2, item3 FROM table2 WHERE role = 2
....

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[ChrisC]

Re: Is there is a way in DB2 to return multiple fields from a singlecase

lenygold via DBMonster.com wrote:

I am truing to concstruct something like this:
>
SELECT id, name, role,
CASE
WHEN role = 1 THEN SELECT item1, item2, item3 FROM table1
WHEN role = 2 THEN SELECT item1, item2, item3 FROM table2
ELSE SELECT item1, item2, item3 FROM table3
END as THIS, THAT, THOSE
FROM roles_tbl
Is this is valid in any DB2 VERSIONS

Well, there are ways. This works:

with t1 as (SELECT item1, item2, item3 FROM table1),
t2 as (SELECT item1, item2, item3 FROM table2),
t3 as (SELECT item1, item2, item3 FROM table3),
SELECT id, name, role, coalesce(t1.ite m1, t2.item1, t3.item1) as
THIS,
coalesce(t1.ite m2, t2.item2, t3.item2) as THAT,
coalesce(t1.ite m3, t2.item3, t3.item3) as THOSE
FROM roles_tbl
LEFT OUTER JOIN t1 ON roles_tbl.role = 1
LEFT OUTER JOIN t2 ON roles_tbl.role = 2
LEFT OUTER JOIN t3 ON roles_tbl.role NOT IN (1, 2)

-Chris

[--CELKO--]

Re: Is there is a way in DB2 to return multiple fields from a singlecase

>Is this is valid in any DB2 VERSIONS <<

This is not valid anywhere; CASE is an expression and not a control
flow statement. Use a UNION to get data from multiple tables.

[Tonkuma]

Re: Is there is a way in DB2 to return multiple fields from a singlecase

I think that CTEs are not neccesary.
You can join the tables in FROM clause.

SELECT rt.id, rt.name, rt.role
, COALESCE(t1.ite m1, t2.item1, t3.item1) AS THIS
, COALESCE(t1.ite m2, t2.item2, t3.item2) AS THAT
, COALESCE(t1.ite m3, t2.item3, t3.item3) AS THOSE
FROM roles_tbl rt
LEFT JOIN
table1 t1
ON <some predicates to relate table1 with roles_tbl>
AND rt.role = 1
LEFT JOIN
table2 t2
ON <some predicates to relate table2 with roles_tbl>
AND rt.role = 2
LEFT JOIN
table3 t3
ON <some predicates to relate table3 with roles_tbl>
AND rt.role NOT IN (1, 2)
;