Serial Number as a part of Resultset

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Hi,

My requirement is as follows:

I have a select query

SELECT USER_ID
,USER_NAME
FROM USER
WHERE USER_ID > 500

Result

USER_ID USER_NAME
11111 Ajesh
22222 Aran
33333 Akhil

The requirement is that I need another column as a part of this result which is nothing but the serial number.

Expected Result

SL_NO USER_ID USER_NAME
01 11111 Ajesh
02 22222 Aran
03 33333 Akhil

Is this possible.Can someone help me in this?

dick scherrer · Posted: Thu May 14, 2009 11:57 am

Hello,

Would this work for you:

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Hi Dick,

Nope that option wont work for me,since there is no Serial Number column in the table.That column values should be generated dynamically when the query is executed.

Anuj Dhawan · Posted: Thu May 14, 2009 2:54 pm

Here are some similar links:

www.ibmmainframes.com/viewtopic.php?t=26832&postdays=0&postorder=asc&start=0
www.ibmmainframes.com/viewtopic.php?t=30398&highlight=serial

Anuj Dhawan · Posted: Thu May 14, 2009 2:58 pm

Or alternatively, to generate the row number in a table,you can use the OLAP function(ROW_NUMBER()).

Anuj Dhawan · Posted: Thu May 14, 2009 3:02 pm

The ROW_NUMBER (or ROWNUMBER) function computes the sequential row number of the row within the window defined by the ordering, starting with 1 for the first row. If the ORDER BY clause is not specified in the window, the row numbers are assigned to the rows in arbitrary order, as returned by the subselect (not according to any ORDER BY clause in the select-statement).

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Hi Anuj,

Thanks for the reply...

I tried OLAP function.The DB2 version is not supporting the same.Hence I am creating a session table with a column as sequence number and then inserting the rows into the session table and then pulling the record from session table.The performance will be worse in case if the number of record is more.Is there any possibility where we can generate a row number using a query?

Anuj Dhawan · Posted: Thu May 14, 2009 4:28 pm

AFAIK, no -- by the way which version of DB2 you are using?

dbzTHEdinosauer · Posted: Thu May 14, 2009 4:29 pm

you say serial number.

what do you mean?
a sequential numbering of rows?
a unique identifier for the row, that is not required to be sequential?

Arun Raj · Posted: Thu May 14, 2009 5:04 pm

ajeshrn,

You can feed the output file from the sql to a sort step and generate the seqnum.

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Hi,

I am looking for a sequence number which is to be generated when we execute the query.This sequence number denotes the row number.
Eg: First row will have 1,Second row will have 2 and so on...

We recently upgraded to Version 9.1

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Hi arcvns,

Here is the scenario where I am using the above logic.

I have an COBOL SP which is being called by the Web Screens.In the screen we have Pagenation.Rows per page is user defined.(ex 200 recs/ page).Suppose a user clicks Page 1 the user should be able to see the first 200 records,page 2 means 201- 400 and so on.So we should have a key which determines the starting point of 201th record,but unfortunately i dont have a key value(Primary or Composite keys) which I can use to determine the starting position.To generate a key I am looking for a sequence number(row number).

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Hi all,

For more clarity and as continuation of the above discussion.I will be using the key value in the select query

Page 1

Select a,b
from table
where row_id between (1 and 200)

Page 2

Select a,b
from table
where row_id (201 and 400)

and so on...

Succor · Posted: Thu May 14, 2009 6:10 pm

Ajeshrn, I am unable to understand your requirement,

dbzTHEdinosauer · Posted: Thu May 14, 2009 6:32 pm

sounds like the query was written,
and then somebody decided to redesign the front-end (page number request)
and now the back-end has to be 'modified' instead of redesigned.

Ajeshrn,

if you would explain (more detail) the flow of events of this front-end service,
you will probably receive better answers.

You defined your needs (first post) yourself without explanation,
and then reject all responses
- which obviously did not apply because no one has any idea what you are trying to do.

Stop making us guess what you are doing.
If you knew enough to come up with a solution,
you would not have asked.
So, don't expect your definition of requirements to elicit a satisfactory answer.

at this point it should be obvious that

ajeshrn · New User Joined: 25 Mar 2009 Posts: 78 Location: India

Hi All,

I am sorry for what happened.I just didn't wanted to confuse you all be saying the whole requirement.Please find my complete logic as below:

We have a web screen which works as front end application.They will pass the input parameter (Rows/page and Page Number).The back end COBOL SP will have these input parameters declared.The SP will be opening a cursor and giving the values to front end screens.

Now, consider the cursor will be retrieving 1000 rows and the user will be able to see only 200rows/page.The logic which I have used in my earlier programs is first open the cursor and order the results by the key value

Select emp_id,emp_name from employee order by emp_id;

When page number is 1,

Select emp_id,emp_name from employee where emp_id >0.This returns all 1000 rows.The Web code is coded in such a way that it will only display the first 200 rows.

When page number is 2.
The web code sends us Page number 2 and rows per page 200.Now in my COBOL SP i have a logic to find the key value here(emp_id) for 200th row.Now to fetch the records we will Select emp_id,emp_name from employee where emp_id > 200 and this goes on.Hence the web code will fetch first 200 records from the 200th position

Here for my problem , I am not able to find a key value like emp_id in this case which I can use.Thats the reason I am trying to generate a sequence number as a part of result set,which I can use as a part of key.

I donno whether this will confuse you more,so just if you can help me in generating a sequence number (row number) as a part of result set that would be great.

Thanks in Advance

Succor · Posted: Fri May 15, 2009 2:49 am

dbzTHEdinosauer · Posted: Fri May 15, 2009 4:47 am

1. rowset retrieval would be your best alternative; if your vsn of db2 supports rowsets.

2. could any of these tables be dynamically updated (INSERT/DELETE) while the user is paging thru 1000's of lines of data? (roflol) If so, pseudo -sequence numbering would not work.

3. If the data is static, or refreshed on a regular interval, suggest adding a column to these 'semi MDC tables'.

a CTT or GTT would need to be rebuilt upon every page request. does not seem a good idea.

Because you are directly invoking a Stored Procedure, you can not save anything from session to session to tell you where you are or were.

this could really become a can-of-worms; some thought needs to be given for the design of such access.

sarangwagh7 · New User Joined: 15 Mar 2007 Posts: 13 Location: Pune

Logic you can code which is not based on DB2 sequence.
It depends on what you are receiving from front end.

If you are getting all values back second time from front end which you have provided on Page 1, then you can use last continuation value in your select clause. (Select emp_id,emp_name from employee where emp_id > continuation value ). So for first time your continuation value will be zero and select query will fetch the first 200 number of records ( For this you need to code FETCH FIRST 200 ROWS ONLY in select ) this "200" value can change according to your Rows/Page. When user will click on Next, your program will receive continuation value as 200 and it will fetch accordingly.

If you are getting only Page No and Rows/Page, then you need to calculate the continuation value accordingly.

Same logic applies for PREV case from front end.

ashimer · Posted: Mon May 25, 2009 4:16 pm