Alternative option to UNION ALL

Question

I have the SQL below:

SELECT '1.1' AS column_a, '1' as column_b, 4 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '1.2' AS column_a, '1' as column_b, 4001 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.1' AS column_a, '2' as column_b, 1 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.1' AS column_a, '2' as column_b, 2 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.1' AS column_a, '2' as column_b, 3 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.2' AS column_a, '2' as column_b, 1001 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.2' AS column_a, '2' as column_b, 1002 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.2' AS column_a, '2' as column_b, 1003 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.2' AS column_a, '2' as column_b, 1004 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.2' AS column_a, '2' as column_b, 1005 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.2' AS column_a, '2' as column_b, 1006 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.2' AS column_a, '2' as column_b, 2001 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.2' AS column_a, '2' as column_b, 2002 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.2' AS column_a, '2' as column_b, 2003 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2.2' AS column_a, '2' as column_b, 2004 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '3.1' AS column_a, '3' as column_b, 3001 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '3.1' AS column_a, '3' as column_b, 3002 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '3.2' AS column_a, '3' as column_b, 3003 AS column_c FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '3.2' AS column_a, '3' as column_b, 3004 AS column_c FROM SYSIBM.SYSDUMMY1

The SQL result is fixed, all the values will be the same.

I am using DB2. Is there a better way to do the same thing without so many duplication in SQL?

you can use VALUES, like `VALUES ('A', 1), ('B', 2), ('C', 2)` — Iłya Bursov, May 24 '16 at 17:36
I get result "DSN10015" when I execute SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1 — Daniel Barral, May 24 '16 at 17:49
When I execute "select * from SYSIBM.SYSVERSIONS" I get error: "SYSIBM.SYSVERSIONS IS AN UNDEFINED NAME" — Daniel Barral, May 24 '16 at 18:18
Oops. That was for iSeries, the LUW flavor is `SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1` — Stavr00, May 24 '16 at 18:31
Possible duplicate of [How to select multiple rows filled with constants?](http://stackoverflow.com/questions/2201170/how-to-select-multiple-rows-filled-with-constants) — Stavr00, May 24 '16 at 18:35

score 1 · Answer 1 · answered May 24 '16 at 17:40

1

As Lashane commented, VALUES might be the answer if your platform and version of DB2 support it.

This works on DB2 for i v7.1

with tbl (col1,col2) 
  as (VALUES ('A', 1), ('B', 2), ('C', 2))
select * from tbl

answered May 24 '16 at 17:40

Charles

21,637
1
20
44

I believe my version of DB2 does not support it. I get the error: ILLEGAL SYMBOL "TBL". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: DSN_INLINE_OPT_HINT – Daniel Barral May 24 '16 at 17:46
@Daniel you are using db2 10 for zOS your version supports it you just need the correct syntax. – Hogan May 24 '16 at 19:37
@daniel -- see my answer. – Hogan May 24 '16 at 20:07
No, the `VALUES` usage here does not work on DB2/zOS. You must use `SYSIBM.SYSDUMMY1` with `UNION` or use the `XMLTABLE` technique below. – Stavr00 May 27 '16 at 12:59

score 1 · Answer 2 · answered May 24 '16 at 18:25

You can use PureXML to parse an embedded XML block into a table:

SELECT * 
FROM XMLTABLE (
'/set/row'
PASSING XMLPARSE(
'<set>
<row a="1.1" b="1" c="4"/>
<row a="1.2" b="1" c="4001"/>
<row a="2.1" b="2" c="1"/>
</set>')
COLUMNS
column_a CHAR(3) PATH '@a',
column_b CHAR(1) PATH '@b',
column_c INTEGER PATH '@c'
) AS X

Alternative option to UNION ALL

2 Answers2