Bdtjtk

I'm trying to run a query via System i Navigator that selects from the combined result of a union.

This works fine:

SELECT AF15VC FROM DB.AF

UNION

SELECT AF15VC FROM BATCH.AFM

So why doesn't this work?

WITH CTE AS (

    SELECT AF15VC FROM DB.AF

    UNION

    SELECT AF15VC FROM BATCH.AFM

)

SELECT *

FROM CTE

And why doesn't this work?

SELECT *

FROM

    (SELECT AF15VC FROM DB.AF

     UNION

     SELECT AF15VC FROM BATCH.AFM

    ) AS AF

In both cases, I get this error:

SQL State: 42601

Vendor Code: -199

Message: [SQL0199]

Keyword UNION
not expected. Valid tokens: ). Cause . . . . . : The keyword UNION
was not expected here. A syntax error was detected at keyword UNION.
The partial list of valid tokens is ). This list assumes that the
statement is correct up to the unexpected keyword. The error may be
earlier in the statement but the syntax of the statement seems to be
valid up to this point. Recovery . . . : Examine the SQL statement
in the area of the specified keyword. A colon or SQL delimiter may be
missing. SQL requires reserved words to be delimited when they are
used as a name. Correct the SQL statement and try the request again.

I tried UNION ALL as well with no change in the result.

Update:

Since people keep thinking I'm not showing the actual queries, I added screenshots. Here is the first query working fine and the second two failing:

Works fine for me on release 7.2

with cte as (

select pmco#, pmmanf

from dtdata.pdpmast

union 

select pmco#, pmmanf

from devqdata.pdpmast

)

select * from cte;

Note: use UNION ALL if you don't have or don't care about duplicates. UNION gets rid of duplicates and if there aren't any means a lot of wasted processing.

Your statements should work fine. Here is a test from my IBM i at v7.2, but it should work the same at v7.1, or at least all the way back to v5r3 based on the documentation that I can find.

create table tablea

  (field1    Char(10),

   field2    Char(10));

create table tableb

  (field1    Char(10),

   field2    Char(10));

insert into tablea

  values ('row1', 'mama'),

         ('row2', 'papa');

insert into tableb

  values ('rowa', 'timmy'),

         ('rowb', 'sissy');

then

select * from tablea

union

select * from tableb;

gives:

FIELD1      FIELD2      

------------------------

row1        mama        

rowb        sissy       

row2        papa        

rowa        timmy       

and

with cte as (

  select * from tablea

  union

  select * from tableb)

select * from cte;

gives

FIELD1      FIELD2      

------------------------

rowb        sissy       

row1        mama        

row2        papa        

rowa        timmy       

and

select * 

from (

  select * from tablea

  union

  select * from tableb) a

gives

FIELD1      FIELD2      

------------------------

rowb        sissy       

row1        mama        

row2        papa        

rowa        timmy       

Well, apparently the system is actually version 5.1, not 7.1 as I had stated in a comment. I mistook my version of System i Navigator for the database engine version (or OS, or whatever it is). As far as I know, the really old version is the problem.