f



Error SQLSTATE=42703

Hello
I using websphere and db2 (v8). I have a problem with this select:
SELECT *
  FROM (
    SELECT ROW_NUMBER() OVER() AS ROWNUM, * 
       FROM S.A AA) AA
  WHERE ROWNUM BETWEEN 5 AND 20;

From console is everything good, no error, but in JDBC (only log) I
got a error:
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0206N
Object "ROWNUM" is not valid in the context where it is used.
SQLSTATE=42703


Anyone could help me find what is wrong?
0
patryk979
2/18/2005 12:58:20 PM
comp.databases.ibm-db2 12448 articles. 3 followers. arunrocks (9) is leader. Post Follow

5 Replies
7768 Views

Similar Articles

[PageSpeed] 3

Patryk wrote:
> Hello
> I using websphere and db2 (v8). I have a problem with this select:
> SELECT *
>   FROM (
>     SELECT ROW_NUMBER() OVER() AS ROWNUM, * 
>        FROM S.A AA) AA
>   WHERE ROWNUM BETWEEN 5 AND 20;
> 
> From console is everything good, no error, but in JDBC (only log) I
> got a error:
> COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0206N
> Object "ROWNUM" is not valid in the context where it is used.
> SQLSTATE=42703
> 
> 
> Anyone could help me find what is wrong?
The error message is a bit surprising, but I think you need AA.* rather 
than only *.

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
0
Serge
2/18/2005 1:24:25 PM
Serge Rielau wrote:
> Patryk wrote:
> The error message is a bit surprising, but I think you need AA.* rather 
> than only *.
> 
Thank for fast answer.
This not help, exception is throw.
I wrote this and again get error in logs:
SELECT AA.C
   FROM (
     SELECT ROW_NUMBER() OVER() AS ROWNUM, AA.C
        FROM S.A AA) AA
   WHERE AA.ROWNUM BETWEEN 5 AND 20;
0
Patryk
2/18/2005 1:43:03 PM
Patryk wrote:
> Serge Rielau wrote:
> 
>> Patryk wrote:
>> The error message is a bit surprising, but I think you need AA.* 
>> rather than only *.
>>
> Thank for fast answer.
> This not help, exception is throw.
> I wrote this and again get error in logs:
> SELECT AA.C
>   FROM (
>     SELECT ROW_NUMBER() OVER() AS ROWNUM, AA.C
>        FROM S.A AA) AA
>   WHERE AA.ROWNUM BETWEEN 5 AND 20;
Very odd. What's you exact system? Her eis what I get:
db2 => connect to test;

    Database Connection Information

  Database server        = DB2/NT 8.2.1
  SQL authorization ID   = SRIELAU
  Local database alias   = TEST

db2 => create table s.a(c int);
DB20000I  The SQL command completed successfully.
db2 => SELECT AA.C
db2 (cont.) =>   FROM (
db2 (cont.) =>     SELECT ROW_NUMBER() OVER() AS ROWNUM, AA.C
db2 (cont.) =>        FROM S.A AA) AA
db2 (cont.) =>   WHERE AA.ROWNUM BETWEEN 5 AND 20;

C
-----------

   0 record(s) selected.

db2 =>

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
0
Serge
2/18/2005 2:12:33 PM
Serge Rielau wrote:
 > Very odd. What's you exact system?

IBM WebSphere Application Server, 5.1.0
Build Number: b0344.02

DB2/NT 8.01.0000

JDBC: IBM DB2 JDBC 2.0 Type 3

I think problem is in WebSphere, not SQL query or JDBC driver (in other 
program using JDBC driver is good).
In DB2 console everything work great.

One more thanks for help.
0
Patryk
2/18/2005 2:39:17 PM
I found same way...
Just pack in another select like this:
SELECT AA.NAME
   FROM (SELECT AA.*
           FROM (SELECT ROW_NUMBER() OVER() AS ROWNUM, AA.*
                   FROM S.A AA) AA
           WHERE ROWNUM BETWEEN 0 AND 20) AA;

Thank you Serge
0
Patryk
2/18/2005 3:19:06 PM
Reply: