DB2 LUW vs zOS: Selecting from nested subselect

SQL0104N An unexpected token “” was found following “”. Expected tokens may include: “CORRELATION NAME”. SQLSTATE=42601

This was the error that I got while attempting to execute a stored procedure under DB2 v8.1 for z/OS. Being quite confused about it, I googled for the answer. I didn’t find one, but I did find this google groups post which made mention of it.

Basically, the situation was that I had

SELECT COUNT(*) FROM
(
SELECT …
UNION
SELECT …
)

And while that works just fine under LUW, z/OS doesn’t like it. The simple solution is to add “AS TBL_1”, or any other name to the parenthetical subselect, which I have done below. Both commands work just fine in LUW, but if you are working under z/OS (or at least version 8.1) you will need to use the bottom example.

SELECT COUNT(*) FROM
(
SELECT …
UNION
SELECT …
) AS TBL_1