How to replace VALUES IN UNPIVOT QUERY

[lenygold via DBMonster.com]

I am running this query at home PC with DB2 EXPRESS 9.5C, but at work on
mainframe i don't
have option VALUES.

What is the best way to UNPIVOT without using VALUES?
Thank's in advance.
Lenny G.
SELECT S.year, Q.quarter, Q.sales
FROM sales1 AS S,
TABLE(VALUES(1, S.q1),
(2, S.q2),
(3, S.q3),
(4, S.q4))
AS Q(quarter, sales);

YEAR QUARTER SALES
----------- ----------- -----------
2005 1 20
2005 2 30
2005 3 15
2005 4 10
2006 1 35
2006 2 31
2006 3 29
2006 4 19
2007 1 41
2007 2 43
2007 3 38
2007 4 25

12 record(s) selected.

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

[Serge Rielau]

lenygold via DBMonster.com wrote:

I am running this query at home PC with DB2 EXPRESS 9.5C, but at work on
mainframe i don't
have option VALUES.

What is the best way to UNPIVOT without using VALUES?
Thank's in advance.
Lenny G.
SELECT S.year, Q.quarter, Q.sales
FROM sales1 AS S,
TABLE(VALUES(1, S.q1),
(2, S.q2),
(3, S.q3),
(4, S.q4))
AS Q(quarter, sales);

The only way I can think of is to do:
(SELECT 1, S.q1 FROM SYSIBM.SYSDUMMY1
UNION ALL
.....) AS Q(quarter, sales)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab