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

numbering the output rows sequentially


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

New User


Joined: 09 Dec 2005
Posts: 33

PostPosted: Mon Jul 09, 2007 11:38 am
Reply with quote

Hi all,

my user wants the output from a select to have a sequence number as the first column in the output. ??? anybody have any ideas how this could be done? (other than trying to change the user's requirements).

thanks guys (and gals)
Back to top
View user's profile Send private message
kussu
Warnings : 1

New User


Joined: 16 May 2007
Posts: 33
Location: India

PostPosted: Mon Jul 09, 2007 7:39 pm
Reply with quote

Hi,

Alter the table to add a new column called "seq no." and pass values to this column.
Then in select statement use this column. it will retrieve those values
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jul 10, 2007 12:29 am
Reply with quote

You should try and get the requirment changed. The sequence is already set by the ORDER BY on your select is it not? You are just providing redundant data.

Adding a column is not the answer. What will keep this column populated and make sure that the value in this column is changed on all the records when a new row gets added to the middle of the logical sequence?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jul 10, 2007 12:35 am
Reply with quote

In order to add a sequence number you would need to add it after the ORDER BY of the select is issued otherwise the sequence order could get put out of order.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jul 10, 2007 8:46 am
Reply with quote

With the count(*) you will get an error like this:

Code:

select count(*) as rowid, first_nm     
from owner.table_t
order by first_nm

DSNT408I SQLCODE = -122, ERROR:  A SELECT STATEMENT WITH NO GROUP BY CLAUSE   
         CONTAINS A COLUMN NAME AND AN AGGREGATE FUNCTION IN THE SELECT CLAUSE
         OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE   
         GROUP BY CLAUSE                                                     
                         


A correctly formatted SQL will give you the following. Basically a count of how many times each result occurs.

Code:

select count(*) as rowid, first_nm
from owner.table_t   
group by first_nm                 
order by first_nm         
       
---------+---------+---------+------
      ROWID  FIRST_NM               
---------+---------+---------+------
          1  a                     
          2  aaaa                   
          1  amber                 
          1  app                   
          1  beryl                 
          1  billie                 
          1  christen               
          1  cong                   
          9  Aaron

Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jul 10, 2007 8:54 am
Reply with quote

The logic behind this being that there is no "natural" rowid attached to a certain row: a certain row can be the 4th result row from a certain query, but the same row could as well be the 6th row from the SAME query on the SAME table when the optimizer chose to implement the query differently! (E.g., with or without the use of an index.)


Assuming you are doing any type of ORDER BY.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jul 10, 2007 9:13 am
Reply with quote

Er "Assuming you aren't doing any type of ORDER BY"


But to continue on my above comments (I have a nasty habit of replying to myself)

Say your table looks like this
Code:

NAME
----
bob
larry
susan
dick


Say you assign a sequence number on your select and get a result set like

Code:

SEQ     NAME
---     ----
1       bob
2       dick
3       larry
4       susan


Now lets say you add Jim. Which of the the two shown results are you expecting from you SELECT? Because both are valid depending on how the optimizer decides to query.

Code:

Set 1                      Set 2       
SEQ     NAME               SEQ     NAME                   
---     ----               ---     ----                 
1       bob                1       bob       
2       dick               2       dick       
3       larry              3       jim
4       susan              4       larry
5       jim                5       susan


If you take kussu's advice and ALTER your table, I'll describe my first problem in more detail.

Your table now looks like this
Code:

SEQ    NAME
---    ----
1       bob
2       larry
3       susan
4       dick


If we add Jim, Kathy, Alan and Sam and our code doesn't handle changing all of the existing sequence numbers (a huge headache when you get beyond say 3 rows) reordering for the new natural order, you will get this and the sequence becomes near meaningless.

Code:

SEQ    NAME
---    ----
1      bob
2      larry
3      susan
4      dick
5      jim
6      kathy
7      alan
8      sam
Back to top
View user's profile Send private message
saratchandran

New User


Joined: 24 Dec 2006
Posts: 3
Location: India

PostPosted: Tue Jul 10, 2007 11:28 am
Reply with quote

Does the user want the output in a dataset?
Then,
Fetch the values using the order by so that the first "RANK" Record,
if he wants the output dataset records to be seq numbered.


SORT FIELDS=COPY
OUTREC FIELDS=(1:SEQNUM,6,ZD,7:Required Input Files Fields)

Here the first rec will be 000001 and so on...

Hope I got the requirement Correctly icon_smile.gif
Back to top
View user's profile Send private message
Nimesh.Srivastava

New User


Joined: 30 Nov 2006
Posts: 78
Location: SINGAPORE

PostPosted: Wed Jul 11, 2007 7:37 am
Reply with quote

nuck,
what is the exact requirement, do you want this to be done in this query itself or you can append the language code also.
What I mean is the user wants to see the a sequence number as a first column of the output so you must be using a cursor to fetch the eligible rows and then displaying / printing to a memory location / dataset / printer. for ex I can suggest something like this
Code:
declare cursor
open cursor
int counter = 0
do
fetch :host variables
print ++counter,:host variables
while fetch is success


Hope this helps
Nimesh
Back to top
View user's profile Send private message
nuck

New User


Joined: 09 Dec 2005
Posts: 33

PostPosted: Wed Jul 11, 2007 11:16 am
Reply with quote

hi Guys,

thanks for the tips - turns out this is only a 'nice-to-have' and not a true requirement.

Interestingly enough, I get this request at least once a year for table extracts. When I tell the users to import the exported table into excel and simply look at the row number, they look at me like I'm stupid....
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Jul 11, 2007 6:39 pm
Reply with quote

Tell them there already is a sequence assigned. It is whatever field is specified in the the ORDER BY and adding an extra number doesn't do much good icon_smile.gif
Back to top
View user's profile Send private message
raghunathns

Active User


Joined: 08 Dec 2005
Posts: 127
Location: rochester

PostPosted: Fri Jul 20, 2007 8:54 pm
Reply with quote

you can try this query. performance wise not good but works

Code:
select tb3.Row# + 1, tb1.key1, tb1.col1, tb1.col2
from tablea tb1,
table(select count(*) as Row# from tablea tb2
        where tb2.key1 < tb1.key1) as tb3
where tb1.col1 = 'value1'
order by tb1.key1
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 To get the count of rows for every 1 ... DB2 3
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
No new posts Build a record in output file and rep... DFSORT/ICETOOL 11
Search our Forums:

Back to Top