Nested Table Expression not working for db2 z/OS

Question

The following query works when in run it in DB2 LUW.

SELECT 
  TEMP1.SINIESTRO, 
  TEMP1.POLIZA
FROM TABLE(VALUES
    ('00000031955190','0107737793'),
    ('00000118659978','0107828212'),
    ('00000118978436','0095878120'),
    ('00000122944473','0106845043')
) TEMP1(SINIESTRO, POLIZA);

When I try to use it in DB2 for z/OS, I get the following error:
SQL Error [42601]: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: WITHIN CARDINALITY OVER ). SQLCODE=-104, SQLSTATE=42601, DRIVER=3.72.24

I have tried CTE as well.

WITH X(foo, bar, baz) AS (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) SELECT * FROM X;

And I get this error:
SQL Error [42601]: ILLEGAL SYMBOL "X". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: DSN_INLINE_OPT_HINT. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.72.24

Any idea what should I change to make it work?

The VALUES statement is not yet available in Db2-for-Z/OS , so replace with a query on sysibm.sysdummy1 or temp table. — mao

mao mao · Accepted Answer · 2021-02-17T20:30:07

The currently shipping versions of Db2-for-Z/OS do not have a VALUES clause like the other Db2 platforms already have.

The Db2-for-Z/OS has a single-row table called sysibm.sysdummy1 which some people use.

However, if you can reference a temporary table instead of sysibm.sysdummy1 then the code looks cleaner.

One awkward way to run your statement on Db2 for Z/OS (other ways exist) is with a CTE that unions rows from sysibm.sysdummy1 , (use union all in the subquery if appropriate):

with temp1 (SINIESTRO, POLIZA)
as
(
   select '00000031955190','0107737793' from sysibm.sysdummy1
   union
   select '00000118659978','0107828212' from sysibm.sysdummy1
   union
   select '00000118978436','0095878120' from sysibm.sysdummy1
   union
   select '00000122944473','0106845043' from sysibm.sysdummy1
)
SELECT 
  TEMP1.SINIESTRO, 
  TEMP1.POLIZA
FROM temp1;

Nested Table Expression not working for db2 z/OS

1 Answers