CODEWITHSUNDEEP
db2row-numberzos
Westy
Westy

Reputation: 727

How do I use ROW_NUMBER in DB2 10 on z/OS?

I am running a SQL query and trying to break the results down into chunks. 

select task_id, owner_cnum
    from (select row_number() over(order by owner_cnum, task_id)
        as this_row, wdpt.vtasks.*
            from wdpt.vtasks)
                where this_row between 1 and 5;

That SQL works with DB2 10.5 on Windows and Linux, but fails on DB2 10.1 on z/OS with the following error messages:

When I run the SQL from IBM DataStudio 4.1.1 running on my Windows machine connected to the database, I am getting:

ILLEGAL SYMBOL "<EMPTY>". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: CORRELATION NAME. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.18.60

When I run my Java program on a zLinux system connecting to the database, I get the following error:

DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=<EMPTY>;CORRELATION NAME, DRIVER=3.65.97

Any ideas what I'm doing wrong?

Upvotes: 1

Views: 2515

Answers (1)

mustaccio
mustaccio

Reputation: 18955

In some DB2 versions you must use a correlation name for a subselect, as suggested by the error message:

select FOO from (
  select FOO from BAR
) as T

Here "T" is the correlation name.

Upvotes: 3

Related Questions