Programming

As a disclaimer, I am not a database person. I have some basic knowledge, but my area is in other fields. Please treat me like I am stupid when it comes to this question.

An Oracle script has been dumped on me, which I have been able to work out an understanding of, but I need to make a change to it. The script takes data from several different tables and puts it all together into a temporary table where various changes are made before it reports what it has. I need to add a simple loop which goes over each row and increments a value until a certain condition is met. This much I can handle, but there is no way to track my place in the table. I want to add a column that simply indexes the table 1,2,3,...,n. I have tried to Google this, but either I could not find it, or I could not understand it. I think it may have been the latter.

The table creation takes the following basic form (there are a lot of values, so copying it all in would be over-kill):

Code:

CREATE TABLE name AS
SELECT a,b,c
FROM x,y,z
WHERE n;

How do I modify this to add an indexing column?

You mean like access has?
1. create a sequence - make sure it has a public synonym so other users can access it
see HELP CREATE SEQUENCE
see help on create synonym
see help on grant to allow others to access the sequence

2.

Code:

select sequencename.nextval from dual;

lets you get the next value from the sequence.

You are gonna have to give us a better example than your select statement, so we can give you a working example of putting the sequence number into your table.

If you are having performance problems consider adding an index to your table.

Sorry, I do not think I follow. It is not a performance issue --- this is only a temporary table that gets thrown away later. After this table is built, I will be adding a section which loops over it, something like:

count number;
count = 1;
select max(index) into max_rows from temp;
while count <= max_rows
loop
update temp set x=y where index=count;
count = count + 1
endloop;

I want to add column of sequential, unique numbers to the table (either during or after creation) that I can use as, basically, my place-holder as I update each row in the table. The select I showed was exactly how the table is built (albeit the original has create table temp as select a.colum1, b.colum2, b.column10 from table1 a, table2 b where a.column1='blah' and b.column2='blah', etc).

- SCA name?
Anyway the sequence is what you want. That is what "numbering' in Oracle is called.

Code:

create sequence mysequence
  minvalue 1
  start with 1
  increment by 1; 
CREATE TABLE name AS
SELECT a,b,c, (select mysequence.nextval from dual)
FROM x,y,z
where n;
drop sequence mysequence;

Dropping and creating tables and sequences just to run a report is not consdiered best practice, ie. running DDL during routine production

But since you already are doing it, why not just keep on with the trend....

You now have a last field which is a number 1... n.

Thanks for the help there. It took a little messing around to get it to work (apparently sequences cannot be used in a select within a select), but eventually I managed to sort things out. It seems awfully complicated for what seems a simple enough operation, but I learned quite a few things out of this.

You can also include rownum in the select. For example:

Code:

SELECT rownum, filed1, field2 FROM table WHERE field3='X';

ROWNUM is the number of the row - it is added by Oracle to every query.