My table has columns named as C1,C2,C3 so on. Each department in the has values only for a specific number of columns. That is, not all columns are used by all departments. In the code below the second cursor gets all the columns associated with a department. Since the nu,ber of column is unknown I am using a variable i to concatenate it with C and get the column name.
When I compile this code I get an error 'ORA-01008: not all variables bound'
i number := 0;
cursor pivotc is select * from tmp_pivoted_data ;
cursor controlc (did number) is select * from tmp_control_table where deptid = did;
fetch pivotc into pivot_row;
exit when pivotc%notfound;
i := i+1;
colname := 'pivot_row.c' || to_char(i);
fetch controlc into control_row;
exit when controlc%notfound;
stmt := 'update tmp_table set rvu=' || :x;
stmt := stmt || ' where ipd_number=' || pivot_row.ipd_number;
stmt := stmt || ' and seq_number=' || pivot_row.seq_number;
stmt := stmt || ' and cost_type=' || control_row.cost_type;
stmt := stmt || ' and category=' || control_row.category;
stmt := stmt || ' and cat_descr=' || control_row.cat_descr;
execute immediate stmt using colname;
i := 0;
I am tryimg to update rows of a table that were pivoted into a temporary table.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!