Home » SQL & PL/SQL » SQL & PL/SQL » how to generate the batch number based on number of records (Oracle 10.2.0.3 database)
how to generate the batch number based on number of records [message #394208] Thu, 26 March 2009 02:06 Go to next message
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 #394209 is a reply to message #394208] Thu, 26 March 2009 02:12 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
one more update on the above request. this has to be done using SQL query only.
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #394237 is a reply to message #394212] Thu, 26 March 2009 04:44 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
thanks for your timely response flyboy. it really helped me. now, i no need to use seqience.
can you please explain me in little detail if i use order by.
i,e. ROW_NUMBER() OVER (ORDER BY <order column(s)>).

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 Go to previous message
flyboy
Messages: 1832
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> 
Previous Topic: Vertical Pivot query (merged 3)
Next Topic: How to turn image files into blob
Goto Forum:
  


Current Time: Wed Dec 07 02:55:06 CST 2016

Total time taken to generate the page: 0.06262 seconds