IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Serial Number as a part of Resultset


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Thu May 14, 2009 11:33 am
Reply with quote

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?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu May 14, 2009 11:57 am
Reply with quote

Hello,

Would this work for you:
Code:
SELECT SL_NO,
       USER_ID,
       USER_NAME
  FROM USER   
 WHERE USER_ID > 500
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Thu May 14, 2009 12:26 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu May 14, 2009 2:54 pm
Reply with quote

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
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu May 14, 2009 2:58 pm
Reply with quote

Or alternatively, to generate the row number in a table,you can use the OLAP function(ROW_NUMBER()).
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu May 14, 2009 3:02 pm
Reply with quote

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).
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Thu May 14, 2009 3:38 pm
Reply with quote

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?
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu May 14, 2009 4:28 pm
Reply with quote

AFAIK, no -- by the way which version of DB2 you are using?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 14, 2009 4:29 pm
Reply with quote

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?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu May 14, 2009 5:04 pm
Reply with quote

ajeshrn,

You can feed the output file from the sql to a sort step and generate the seqnum.
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Thu May 14, 2009 5:06 pm
Reply with quote

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
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Thu May 14, 2009 5:13 pm
Reply with quote

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).
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Thu May 14, 2009 5:25 pm
Reply with quote

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...
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Thu May 14, 2009 6:10 pm
Reply with quote

Ajeshrn, I am unable to understand your requirement,
Quote:

I am looking for a sequence number which is to be generated when we execute the query. This sequence number denotes the row number.

As you want the serial numbers to get generated while selecting, how would that ensure the result set you would be sending to the front end would be in a sequential order.
The front end would provide you with some identifier which would help you to determine which set of rows you should be sending back. i.e. either 1-200 ,201-400 and so on.
Since you do not have a key to select on(which is some what not advisable) ,i believe you should have an extra column ,where the ROW ID gets inserted in the beginning and you retrieve it based on the flag sent to you by the front end.

WTH
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 14, 2009 6:32 pm
Reply with quote

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
Quote:
Page 1

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


this will not work,
unless you save your initial selected data in yet another table with a generated row number
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Thu May 14, 2009 7:09 pm
Reply with quote

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
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Fri May 15, 2009 2:49 am
Reply with quote

Quote:
Here for my problem , I am not able to find a key value like emp_id in this case which I can use.

I would say you were lucky in the first scenario, imagine if values for your emp_id field would have been 1,6,10... then your 200 /page logic would not have worked.
Quote:

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.

How will generating a sequence number would help your cause,it needs be to be linked to every row in the table in a similar way your emp_id worked, which was the key.
Either you have to insert ROW_ID as a column before fetch or do what you were doing using session table if it is advisable.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri May 15, 2009 4:47 am
Reply with quote

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.
Back to top
View user's profile Send private message
sarangwagh7

New User


Joined: 15 Mar 2007
Posts: 13
Location: Pune

PostPosted: Wed May 20, 2009 4:34 pm
Reply with quote

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.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon May 25, 2009 4:16 pm
Reply with quote

Code:

I tried OLAP function.The DB2 version is not supporting the same


9.1 should support OLAP ..maybe new function mode is not active ... try row_number() over() ..... or else your best bet would be scrollable cursors which should be handled well by your front end ...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts Generate random number from range of ... COBOL Programming 3
No new posts Increase the number of columns in the... IBM Tools 3
No new posts Cobol program with sequence number ra... COBOL Programming 5
Search our Forums:

Back to Top