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):
CREATE TABLE name AS
SELECT a,b,c
FROM x,y,z
WHERE n;
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
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.
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.