Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need help on a query!!!

Re: Need help on a query!!!

From: Radoslav Rusinov <radoslav_rusinov_at_hotmail.com>
Date: 9 Oct 2002 05:38:51 -0700
Message-ID: <698179bc.0210090438.5b028384@posting.google.com>


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);

log_dates_array log_dates_type;
BEGIN
SELECT MAX(COUNT(userid)) INTO v_user_count FROM my_table GROUP BY userid
IF v_user_count >=1 THEN
  v_str_ddl := 'CREATE TABLE temp_table(userid NUMBER NOT NULL,';   FOR i IN 1..v_user_count LOOP
   v_str_ddl := v_str_ddl||' login_date'||to_char(i)||' DATE,';   END LOOP;
   v_str_ddl := substr(v_str_ddl,1,len(v_str_ddl)-1; --remove the last comma

   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;

  v_str_dml := substr(v_str_dml,1,len(v_str_dml)-1; --remove the last comma
  v_str_dml := v_str_dml||' WHERE userid='||c_rec.userid;   EXECUTE IMMEDIATE v_str_dml;
 END LOOP;
 COMMIT;
ELSE
dbms_output.put_line('The table my_table is empty'); END IF;
END;
/
DESC temp_table
SELECT userid,login_date1,....... FROM temp_table; DROP TABLE temp_table;

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US