• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

row_number not working when using multiple joins [db2]

 
Julien Martin
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
I have a complex join the result of which I am going to insert in another table. I would like to number the rows from 1 to n as they are fetched by my sql query. I tried using the row_number to no avail. Can anyone please help?

Here is the query:




Can anyone tell me why db2 says:

"an olap function is not supported for this query"?

Any clue welcome,

Julien.
[ May 29, 2008: Message edited by: Julien Martin ]
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How are you building the insert statement?
 
Julien Martin
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I meant to write it later on, once the select would work.
Have you got any suggestion?
Julien.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, I more meant are you trying to do a direct insert such as "INSERT INTO ... (SELECT FROM ...)". Such queries can work but will often be highly database dependent ergo non-portable.

The other options is to perform the inserts via a JDBC query, in which case getting the row number is trivial and better handled by the java application (no need to load the database with information it does not need) such as:

[ May 29, 2008: Message edited by: Scott Selikoff ]
 
Julien Martin
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks,
it does not matter whether it is db dependent. I'd rather have it all-sql.
any idea?
Julien.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nope, other than it always matters if its database-dependent IMHO.
 
Julien Martin
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
the thing is my company uses db2 and only db2 and I need to show something functional to my boss before moving to java.
thanks anyway,
regards,
Julien
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your not really 'moving to java' if 99% of your query relies on SQL. Its not really JDBC. It'd be like writing a 10 page SQL statement as a single string and calling it from JDBC. You mine as well have stored procedures.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic