| how to generate the batch number based on number of records [message #394208] |
Thu, 26 March 2009 02:06  |
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
|
Junior Member |
|
|
Hi All,
requirement: i have created one sequence to generate the batch numbers.i have to get the nextval of the sequence for batch number (this column is not part of the table. its dynamic column/alias column) only for every 50000 records of a table.
detail description:
for example, if i have 10 records in a table. And, if i want to generate the batch number for every 2 records, then the output should look like belo.
batch-number qty
----------- ----
1 10
1 20
2 30
2 40
3 50
3 60
4 70
4 80
5 90
5 100
Please help me on this. Please let me know if you need more info.
|
|
|
|
|
|
| Re: how to generate the batch number based on number of records [message #394212 is a reply to message #394208] |
Thu, 26 March 2009 02:46  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Depends on whether the output is sorted (the query contains ORDER BY clause).
If no, use ROWNUM. If yes, use ROW_NUMBER() OVER (ORDER BY <order column(s)>).
The rest is simple arithmetics: SQL> SELECT rownum, trunc((rownum+1)/2)
2 FROM all_objects
3 WHERE rownum <= 6;
ROWNUM TRUNC((ROWNUM+1)/2)
---------- -------------------
1 1
2 1
3 2
4 2
5 3
6 3
6 rows selected.
SQL> |
|
|
|
|
|
| Re: how to generate the batch number based on number of records [message #394267 is a reply to message #394208] |
Thu, 26 March 2009 06:14 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
ROW_NUMBER function is (as all Oracle functions) already documented. It is described in SQL Reference book, available with many other books e.g. online on http://tahiti.oracle.com/. Please, study it there. There are many examples there too.
Just a little demonstration what I was thinking about: SQL> -- without ORDER BY
SQL> SELECT object_name, rownum
2 FROM all_objects
3 WHERE rownum <= 10;
OBJECT_NAME ROWNUM
------------------------------ ----------
DUAL 1
DUAL 2
SYSTEM_PRIVILEGE_MAP 3
SYSTEM_PRIVILEGE_MAP 4
TABLE_PRIVILEGE_MAP 5
TABLE_PRIVILEGE_MAP 6
STMT_AUDIT_OPTION_MAP 7
STMT_AUDIT_OPTION_MAP 8
MAP_OBJECT 9
RE$NV_LIST 10
10 rows selected.
SQL> -- with ORDER BY
SQL> SELECT object_name, rownum, ROW_NUMBER() OVER (ORDER BY object_name)
2 FROM all_objects
3 WHERE rownum <= 10
4 ORDER BY object_name;
OBJECT_NAME ROWNUM ROW_NUMBER()OVER(ORDERBYOBJECT_NAME)
------------------------------ ---------- ------------------------------------
DUAL 2 1
DUAL 1 2
MAP_OBJECT 9 3
RE$NV_LIST 10 4
STMT_AUDIT_OPTION_MAP 7 5
STMT_AUDIT_OPTION_MAP 8 6
SYSTEM_PRIVILEGE_MAP 3 7
SYSTEM_PRIVILEGE_MAP 4 8
TABLE_PRIVILEGE_MAP 6 9
TABLE_PRIVILEGE_MAP 5 10
10 rows selected.
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
