November 7, 2008 at 5:56 am
Firstly excuse my complete ignorance here!
I need to run a query against a db to force the re-aggregation of some data. I have something that should do the job, but it's for an Oracle DBMS and I need it to work with SQL.
I have been trying to translate it using my very very basic knowledge but have hit a wall. Hopefully someone here will be able to make sense of it!
Both the original Oracle query and my attempt so far at translating it are below. I know that my attempt is failing at least partly because I am trying to select from a table variable, but I'm not sure how to use dynamic sql to achieve what I need. I'm also not sure how to do the date conversion.
Here is the Oracle:
---------------------------------------------------
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
/
spool off
-----------------------------------------------
Now for the funny part, here is my effort so far at translating it:
-----------------------------------------------
declare@vname varchar(50),
@aname varchar(50),
@tname varchar(50)
declare cur cursor for
select a.rep_view_id, a.par_agg_col_name, b.table_name
from dm.report_view as a, dm.rep_to_tab as c, dm.report_table as 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'
open cur
fetch next from cur into @vname, @aname, @tname
while @@fetch_status = 0
begin
insert into dm.pending_agg(rep_view_id, agg_key, cnumber)
select distinct @vname, @aname, 0
from @tname where select convert(end_time, 'YYYYMMDD') >= '20080901'
fetch next from cur into @vname, @aname, @tname
end
close cur
deallocate cur
-----------------------------------------------
Any assistance anyone can provide would be massively appreciated!
November 7, 2008 at 9:28 am
Just got this working by doing the following:
--------------------------------------------------------------
declare @vname varchar(50),
@aname varchar(50),
@tname varchar(50),
@insagg varchar(200)
declare cur cursor for
select a.rep_view_id, a.par_agg_col_name, b.table_name
from dm.report_view as a, dm.rep_to_tab as c, dm.report_table as 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'
open cur
fetch next from cur into @vname, @aname, @tname
while @@fetch_status = 0
begin
set @insagg = 'insert into dm.pending_agg(rep_view_id, agg_key, cnumber)
select distinct ' + @vname +','+ @aname +', 0
from dm.'+ @tname + ' where (select convert(varchar(10), end_time, 112) AS [YYYYMMDD]) >= ' + Str(20080901)
exec (@insagg)
fetch next from cur into @vname, @aname, @tname
end
close cur
deallocate cur
--------------------------------------------------------
Not sure if hat's the most efficient way of doing things, but it certainly did the job!
November 7, 2008 at 10:52 am
I'm not sure how you got that to work. It still looks Oraclish to me. If it is a SQL Server 2005 database, there is probably a better, non-cursor way to do it.
Read the article referenced in my sig block, it will provide you with tips on how best to ask for help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply