Using a value in the data base as a variable.

[peterneve]

Hi All,

I have posted a couple of times but in the wrong forum so hopefully its 3rd time lucky!!

I have an oracle 8i database that i would like to query and update.
There is a table which contains three frequencies which also reference the other frequncies and other arbitrary data.

FREQ OTHER_FREQ OTHER_COL OTHER_COL ETC...
1 2
1 3
2 1
2 3
3 1
3 2

I would like to add a forth frequency.
i thought that i could use an array and have checked out o'reilly's sql book which couldnt help me.
So i have tried to create a temporary table with the frequencies in it, i would like to read each one in turn and run two insert statements.
the code i have is below

create table freq_temp as (select freq from all_freqs ) order by freq asc;
declare

oldfreq  number(3) := 12;
newfreq  number(3) := 40;
rn         number(3) := 1;

      
begin

      for in 1 to (select count (*) from freq_temp)
      loop;
      oldfreq = (select * from freq_temp where rownum=rn);

      insert into all_feq values (newfreq,oldfreq,1,1,1);        
      insert into all_freq values (oldfreq,newfreq,1,1,1);
      rn = rn + 1;
      end loop;
end

I have also found that rownum does not select on rownum,
eg ROWNUM=1 is ok, but ROWNUM=3 gives 0 rows returned.
however ROWNUM<=3 will return 3 rows.

is there another way i can complete this iteration?

TIA

Pete

 

[SantaMufasa]

Pete,

Your PL/SQL syntax and your logic in the code above each have several issues...I cannot tell what you want "oldfreq = (select * from freq_temp where rownum=rn);" to do since you are trying to assign probably 5 table columns to a single number variable.

But, if you are simply trying to create two rows of data for each existing row in your "all_freq" table, using the initial values that appear in your declared variables, then you don't even need PL/SQL...you can do it simply with the SQL INSERT statement that follows this SELECT from your original table:

SQL> select * from all_freq;

      FREQ OTHER_FREQ OTHER_COL1 OTHER_COL2 OTHER_COL3
---------- ---------- ---------- ---------- ----------
         1          2          0          0          0
         1          3          0          0          0
         2          1          0          0          0
         2          3          0          0          0
         3          1          0          0          0
         3          2          0          0          0

6 rows selected.

insert into all_freq
select 40,12,1,1,1
  from all_objects
 where rownum <= (select count(*)*2 from all_freq);

12 rows created.

SQL> select * from all_freq;

      FREQ OTHER_FREQ OTHER_COL1 OTHER_COL2 OTHER_COL3
---------- ---------- ---------- ---------- ----------
         1          2          0          0          0
         1          3          0          0          0
         2          1          0          0          0
         2          3          0          0          0
         3          1          0          0          0
         3          2          0          0          0
        40         12          1          1          1
        40         12          1          1          1
        40         12          1          1          1
        40         12          1          1          1
        40         12          1          1          1
        40         12          1          1          1
        40         12          1          1          1
        40         12          1          1          1
        40         12          1          1          1
        40         12          1          1          1
        40         12          1          1          1
        40         12          1          1          1

18 rows selected.

If, instead, you want some value from the pre-existing row(s) to appear in the new row, then let me know, and we can adjust the code accordingly.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[peterneve]

Hi Mufasa,

thanks for the prompt reply.
I am trying to use existing data in the table. sorry my explanation was a bit naff.
i want to add another frequency, 4. once the query is run this is how i want the table to look, the table name is
"all_freqs".

FREQ OTHER_FREQ
1 2
1 3
1 4
2 1
2 3
2 4
3 1
3 2
3 4
4 1
4 2
4 3

so in my code i create the table "freq_temp" so i have just 3 rows
FREQ
1
2
3

Then I wish to select the first cell copy it to a variable, the new frequency variable is already declared, then add 2 rows (in the table above it would be row 3 and 10) and then iterate through all frequencies.
I thought ROWNUM would allow me to do this however it doesnt!

cheers

Pete

 

[SantaMufasa]

In that case, here is code that should do what you want:

SQL> select * from all_freq2;

      FREQ OTHER_FREQ OTHER_COL1 OTHER_COL2 OTHER_COL3
---------- ---------- ---------- ---------- ----------
         1          2          0          0          0
         1          3          0          0          0
         2          1          0          0          0
         2          3          0          0          0
         3          1          0          0          0
         3          2          0          0          0

6 rows selected.

insert into all_freqs
select distinct freq,4,1,1,1
  from all_freqs
union
select distinct 4,freq,1,1,1
  from all_freqs;

6 rows created.

select * from all_freqs
order by 1,2;

      FREQ OTHER_FREQ OTHER_COL1 OTHER_COL2 OTHER_COL3
---------- ---------- ---------- ---------- ----------
         1          2          0          0          0
         1          3          0          0          0
         1          4          1          1          1
         2          1          0          0          0
         2          3          0          0          0
         2          4          1          1          1
         3          1          0          0          0
         3          2          0          0          0
         3          4          1          1          1
         4          1          1          1          1
         4          2          1          1          1
         4          3          1          1          1

12 rows selected.

Is this what you wanted?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[peterneve]

Hi Dave,

Thanks very much, your code worked a treat and is alot neater than mine.
I didnt understand the Union command, but have just looked it up on the web and its cool

thanks again,

Pete