| View previous topic :: View next topic |
| Author |
Message |
tcurrier
Intermediate
Joined: 10 Feb 2006
Posts: 188
Topics: 68
|
 Posted: Tue Nov 01, 2011 1:27 pm Post subject: column of generated sequence numbers ? |
 |
|
Is there a way to insert a column of sequence numbers in QMF, so that each row returned will have a number associated with it?
| Code: | New Seq # column:
FIELDA FIELDB FIELDC 000001
FIELDA FIELDB FIELDC 000002 |
Thanks for any help. |
|
| Back to top |
|
 |
Sqlcode
Intermediate
Joined: 15 Dec 2006
Posts: 157
Topics: 38
|
| Posted: Tue Nov 01, 2011 1:34 pm Post subject: |
|
|
tcurrier,
See if below helps... DB2 V9
If you need leading zeros, you need to cast sequence number for char and use digits.
| Code: | SELECT FIELDA,FIELDB,ROW_NUMBER () OVER () AS SEQ_NUM
FROM TABLE |
Thanks, |
|
| Back to top |
|
|
tcurrier
Intermediate
Joined: 10 Feb 2006
Posts: 188
Topics: 68
|
| Posted: Tue Nov 01, 2011 5:06 pm Post subject: |
|
|
| I only have version 8. Any chance of getting this functionality in V8 ? |
|
| Back to top |
|
|
GuyC
Supermod
Joined: 27 May 2011
Posts: 35
Topics: 0
|
| Posted: Wed Nov 02, 2011 5:44 am Post subject: |
|
|
only a not so performant one :
| Code: | SELECT FIELDA,FIELDB
,(select 1+count(*) from table1 B where B.uniquesortkey < A.uniquesortkey)
FROM TABLE1 A
order by uniquesortkey |
at least I think you can select in the column list in v8
otherwise :
| Code: | SELECT FIELDA,FIELDB
,seq
FROM TABLE1 A
join table (select 1+count(*) as seq from table1 B where B.uniquesortkey < A.uniquesortkey) C on 1=1
order by uniquesortkey |
|
|
| Back to top |
|
|
tcurrier
Intermediate
Joined: 10 Feb 2006
Posts: 188
Topics: 68
|
| Posted: Wed Nov 02, 2011 8:59 am Post subject: |
|
|
I got the following error using the second query :
| Code: | SELECT POL_NUM, POL_SUFFIX
,SEQ
FROM H2591DB.TCSAU_GENI A
JOIN TABLE (SELECT 1+COUNT(*)
AS SEQ FROM H2591DB.TCSAU_GENI B
WHERE B.UNIQUESORTKEY < A.UNIQUESORTKEY) C ON 1=1
ORDER BY UNIQUESORTKEY
QUERY MESSAGES:
Column B.UNIQUESORTKEY is not in any table named in the query.
*** END *** |
|
|
| Back to top |
|
|
GuyC
Supermod
Joined: 27 May 2011
Posts: 35
Topics: 0
|
| Posted: Wed Nov 02, 2011 12:11 pm Post subject: |
|
|
no comment, just this :
| Code: | SELECT POL_NUM, POL_SUFFIX
,SEQ
FROM H2591DB.TCSAU_GENI A
JOIN TABLE (SELECT 1+COUNT(*)
AS SEQ FROM H2591DB.TCSAU_GENI B
WHERE B.POL_NUM< A.POL_NUM) C ON 1=1
ORDER BY POL_NUM |
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
