how to sequence the rows of a select?

jctgf · Active User Joined: 04 Nov 2006 Posts: 109

hi,

is there any function in db2 that provides a sequential number for each row retrieved in a select statment?

example:

1) select without the function i want:

select name, cityname from table where zipcode = 123

bill miami
mark nyc
ster omaha

2) select with the function i want:

select ???, name, cityname from table where zipcode = 123

001 bill miami
002 mark nyc
003 ster omaha

thanks,
jc

stodolas · Posted: Sun Dec 16, 2007 10:40 pm

Your row number could change for bill miami each time since you don't have an order by. Even if you had an order by on the name, if you add Albert chicago bill would have a different number the next time you run your query

jctgf · Active User Joined: 04 Nov 2006 Posts: 109

hi,

thanks, but is there any function in DB2 that provides this row number?

i don't mean the rowid number, but a row number generated by the select command.

it should be ascendent and start from 1 with a increment of 1.

thanks again,
jc

stodolas · Posted: Mon Dec 17, 2007 8:44 am

I can't think of a way to do it and about 10 minutes searching on Google seemed to confirmed that. It is probably possible if you write your own function or stored procedure and have a DBA put it into the database for you.
I may be missing something, but I can't see a generated row number serving any purpose in processing the data. The table should already have a primary key that can uniquely identify any row in the table without adding these generated numbers you desire.

A number made up on the fly on each query doesn't have any carry over meaning from one query to the next because it is almost assured to change.

Is there a business requirement behind this? If you are running the query through Cobol, then you can keep your own counter. If you are doing ad hoc queries, what does the a row number generated by the select do for you, other than to give a count and for that you could do a SELECT COUNT(*) FROM table WHERE same conditions as your original query.

jctgf · Active User Joined: 04 Nov 2006 Posts: 109

hi,

it's a little bit complicated to explain ...

we have a problem with the 3 layer architerture in our company.

we have a pseudo-conversational enviroment and the caller program usually is a NATURAL one. the called program is coded in COBOL but every time it returns control to the caller, the cursor is closed.

at the next call, the cursor must be opened again and it's a bad solution in terms of performance.

i am trying to find the "less worst" solution.

yes, i can use a counter in the COBOL ws but i just wondered if db2 could provide the counter to me.

thanks a lot,
jc

Ritesh Chopra · New User Joined: 15 Oct 2007 Posts: 13 Location: India

Do you want to count the rows, so that when the program gets the control back u can fetch the rows after the row you had already fetched in previous fetch ?
Please elaborate more oon problem.

stodolas · Posted: Mon Dec 17, 2007 5:32 pm

Let me guess, there are about 50 rows and program a calls program B and program B can only return 10 rows at a time to program A?

Order your cursor by primary key. On subsequent calls to B, pass in the highest primary key from the last call. Make sure program B has a WHERE that allows you to pass in the highest primary key that you retrieved on the last call.

Either this is homework or 2 people are working on exactly the same problem at the same time. People were just replying to an identical topic using a natural program and a conversational enironment and cursor that gets closed.

stodolas · Posted: Mon Dec 17, 2007 5:38 pm

Oh wait you are double posting on your same topic:

ibmmainframes.com/viewtopic.php?t=26787&highlight=

TG Murphy · Posted: Mon Dec 17, 2007 9:03 pm

You say "at the next call the cursor must be opened again."

Question: Does this next call happen within the same task? Or does it happen after the pseudo return (ie. after the user presses F8 to advance to the next page?)

If it happens with the same task - and your cursor is being closed, is it possible that Natural is issing a SYNCPOINT? Here is an long shot experiment to try: Use the WITH HOLD on your cursor declaration and see if that avoids the need to re-open the cursor.

enrico-sorichetti · Posted: Mon Dec 17, 2007 10:15 pm

I would say that the probability might be low, but ...
what if some other task inserted something in between...
relying on a simple sequence expectation is not wise..

what about storing the full result of the query in temporary storage
an do the terminal paging from there
( obviously if the number of the retrieved rows is not unreasonably high )

jctgf · Active User Joined: 04 Nov 2006 Posts: 109

Ritesh Chopra · New User Joined: 15 Oct 2007 Posts: 13 Location: India

Hi jctgf,
What stodolas wrote is correct.
I will explain it this way
The best way to do it is:
suppose you have a program 'A' which retrives and sends data to 'B'.
Now A can send only 10 rows at a time.
What can be done is pass back from 'A' data that indicates which was the last data item.
Then in the sql query you can compare,
your table data > last data.
as the data in the table is in sorted order.

or if u have more than one field to compare the
field1 concat field2 concat f3........ > last data.

This way you can manage to fetch the data starting from the next row.
Experts please correct me.

Ritesh Chopra · New User Joined: 15 Oct 2007 Posts: 13 Location: India

Hi enrico-sorichetti,
Please explain in detail what u want to say.
Is it that what if some new rows are inserted between two fetches ?
Any ways if you buffer the data, the new inserted rows won't be fetched as the buffer does not have them.

enrico-sorichetti · Posted: Tue Dec 18, 2007 3:23 pm

the approach of buffering/saving the result of the query has two objectives

Guarantee the consistency of the results ( totals for example )

Give the end user the possibility of scrolling back and forth thru the data
( without repeating the query )
much more simple than all the cursor/fetch management implied
by the O/P approach

if rows get inserted delete in between all the considerations made on the
relative sequence number WILL be wrong
( the probability is low.. but no reason to risk )

jctgf · Active User Joined: 04 Nov 2006 Posts: 109

jctgf · Active User Joined: 04 Nov 2006 Posts: 109