|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
If you want to sequentially number the records in a table, putting the numbers in a field of the table, you can use the rownum pseudocolumn, doing something like "update inv_recs set dline = rownum;". However, you can't guarantee that the records will get the same number as the order in which they were entered into the table. Also, you can't use the "order by" clause in an update statement. To solve this, you can put an unnamed PL/SQL block into your SQL script to do the numbering, using cursors and for loops and other looping constructs that are available in PL/SQL.
For example, I have a table (inv_hdrs) of invoice numbers and a table (inv_recs) of invoice detail lines associated with those invoices. I wanted to number (dline) the detail lines from 1 through N for each invoice, ordered by a sequence number field (seq_num) which may have gaps in the numbering or may be repeated or may be null. I also wanted to exclude from the numbering any detail line with a zero amount, as well as the entire invoice if it had a zero total amount. The following snippet of PL/SQL was used to do this in my SQL script (be sure to include the "/" after the block to execute it; otherwise, it just gets parsed but not run):
declare
cursor hdr_cur is select invh_code from inv_hdrs where total != 0.00;
cursor rec_cur(code_in varchar2) is select rowid row_id from inv_recs
where invh_code = code_in and amt != 0.00 order by nvl(seq_num,0);
i integer;
begin
for hdr_rec in hdr_cur
loop
i := 0;
for rec_rec in rec_cur(hdr_rec.invh_code)
loop
i := i + 1;
update inv_recs set dline = i where rowid = rec_rec.row_id;
end loop;
end loop;
end;
/
This Page was Last Updated on 06/04/07
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
