OK I found a script for 7.2 which should achieve my aim, however it’s for an Oracle DBMS. Long shot I know, but is anyone clever with both Oracle and SQL able to tell me how to achieve the same thing in SQL?!
whenever sqlerror exit failure
set serveroutput on
spool update
create or replace procedure EXECUTE_IMMEDIATE(cmd in varchar2) is
cur integer;
rc integer;
begin
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur,cmd,dbms_sql.native);
rc := dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
exception
when others then
if dbms_sql.is_open(cur) then
dbms_sql.close_cursor(cur);
end if;
raise;
end;
/
declare
cur integer;
vname varchar2(50);
aname varchar2(50);
tname varchar2(50);
rc integer;
begin
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur,
'select a.REP_VIEW_ID, a.PAR_AGG_COL_NAME, b.TABLE_NAME '||
'from REPORT_VIEW a, REP_TO_TAB c, REPORT_TABLE b '||
'where a.PAR_REP_VIEW_ID is not null '||
'and a.PAR_REP_VIEW_ID=c.REP_VIEW_ID '||
'and c.TABLE_ID=b.TABLE_ID '||
‘and b.INFO_TYPE=’‘TIME_DIM’’ '||
‘and b.PHYSICAL_TYPE=’‘TABLE’’ '||
‘and a.PAR_AGG_COL_NAME=’‘AGG_BY_HOUR’’ ',dbms_sql.native);
dbms_sql.define_column(cur, 1, vname, 50);
dbms_sql.define_column(cur, 2, aname, 50);
dbms_sql.define_column(cur, 3, tname, 50);
delete PENDING_AGG;
rc := dbms_sql.execute(cur);
LOOP
IF dbms_sql.fetch_rows(cur) > 0 then
dbms_sql.column_value(cur, 1, vname);
dbms_sql.column_value(cur, 2, aname);
dbms_sql.column_value(cur, 3, tname);
EXECUTE_IMMEDIATE(‘insert into PENDING_AGG( REP_VIEW_ID, AGG_KEY, CNUMBER) select distinct ‘||vname||’,’||aname||', 0 ’
||'from ‘||tname||’ where to_char(END_TIME, ‘‘YYYYMMDD’’) >= ‘‘yyyymmdd’’ ');
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.close_cursor(cur);
DBMS_OUTPUT.PUT_LINE(‘PENDING_AGG table updated’);
commit;
end;
/
drop procedure EXECUTE_IMMEDIATE
/