Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Need help on a query!!!
DECLARE
TYPE log_dates_type IS TABLE OF my_table.login_date%TYPE;
v_user_count NUMBER; v_str_ddl VARCHAR2(1000); v_str_dml VARCHAR2(1000);
v_str_ddl := v_str_ddl||')';
EXECUTE IMMEDIATE v_str_ddl;
FOR c_rec IN (SELECT userid FROM my_table GROUP BY userid ORDER BY
COUNT(userid) DESC) LOOP
INSERT INTO temp_table(userid) VALUES (c_rec.userid);
SELECT login_date BULK COLLECT INTO log_dates_array
FROM my_table
WHERE userid=c_rec.userid;
v_str_dml := 'UPDATE temp_table SET';
FOR j IN log_dates_array.FIRST..log_dates_array.LAST LOOP v_str_dml := v_str_dml||' login_date'||to_char(j)||'='||log_dates_array(i)||','; END LOOP;
I hope this will help, but it's not working in the way that I want. I
try to create updatable materialized view, but I cannot because the
select clause have group by clause. Save this code in sql file and put
SPOOL ON to save results in other file. The problem is that you must
create new table, unfortunately he is cannot be snapshot or
view(because you don't know how much columns he will have). And the
problem is that you cannot create snapshot and insert data with one
SELECT clause. And because the SELECT have a GROUP BY clause you
cannot update it afterwards.
That is my propose. I hope this will help!
Received on Wed Oct 09 2002 - 07:38:51 CDT