|
View previous topic :: View next topic
|
| Author |
Message |
nuck
New User
Joined: 09 Dec 2005
Posts: 33
|
|
|
|
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 |
|
 |
kussu
Warnings : 1
New User
Joined: 16 May 2007
Posts: 33
Location: India
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| 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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
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 |
|
|
saratchandran
New User
.jpg)
Joined: 24 Dec 2006
Posts: 3
Location: India
|
|
|
|
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  |
|
| Back to top |
|
|
Nimesh.Srivastava
New User
Joined: 30 Nov 2006
Posts: 78
Location: SINGAPORE
|
|
|
|
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 |
|
|
nuck
New User
Joined: 09 Dec 2005
Posts: 33
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| 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 |
|
| Back to top |
|
|
raghunathns
Active User
.jpg)
Joined: 08 Dec 2005
Posts: 127
Location: rochester
|
|
|
|
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 |
|
|
|
|
