Warehouse... need min and max for each data field per table...
-
I want to do a high level check of the numeric values that are making it into my fact tables by looking at the min/max for each field/table combination.
Here's what I have so far, but it's not working I think because the dynamically generated SQL won't insert into my temp table.
PLEASE HELP!
------------------
create table #minmax (
fact varchar(32),
field varchar(32),
minval float,
maxval float)
insert into #minmax (fact, field)
select object_name([id]), [name]
from syscolumns
where object_name([id]) in ('fc_statedy3')
--('fc_statedy1',
--'fc_statedy2',
--'fc_statedy3',
--'fc_statedy4',
--'fc_loan',
--'fc_resp',
--'fc_source')
and [name] not like '%WK'
and [name] not like '%BK'
and xtype in (48, 52, 56, 59, 62, 106, 108, 127)
declare factfield cursor for
select fact, field from #minmax
declare @tempfact varchar(32)
declare @tempfield varchar(32)
declare @sqlstr nvarchar(1024)
open factfield
fetch next from factfield into @tempfact, @tempfield
while @@fetch_status = 0
begin
set @sqlstr = N'insert into #minmax(minval, maxval) q
select min(' + @tempfield+ N'), max(' + @tempfield + N')
from ' + @tempfact + N' where q.fact = '+ @tempfact + N' and q.field = ' + @tempfield
exec sp_executesql @sqlstr
fetch next from factfield into @tempfact, @tempfield
end
select * from #minmax
go
-
Since you are using a temp (#) table, change them from a local temp table (#) to a global temp table (##).
hth
Lynn
-
Thanks Lynn. But it turns out you CAN insert into a temp table (the single # kind) using dynamic sql, and I was just being an idiot.
Here's the working version of that code.
drop table #minmax
go
create table #minmax (
fact varchar(32),
field varchar(32),
minval float,
maxval float)
declare @tempfact varchar(32)
declare @tempfield varchar(32)
declare @sqlstr nvarchar(1024)
declare factfield cursor for
select object_name([id]), [name]
from syscolumns
where object_name([id]) in
('fc_loancurrentstatedy1',
'fc_loancurrentstatedy2',
'fc_loancurrentstatedy3',
'fc_loancurrentstatedy4',
'fc_loanorigination',
'fc_responsedetail',
'fc_sourcehistory')
and [name] not like '%WK'
and [name] not like '%BK'
and xtype in (48, 52, 56, 59, 62, 106, 108, 127)
open factfield
fetch next from factfield into @tempfact, @tempfield
while @@fetch_status = 0
begin
set @sqlstr = N'insert into #minmax(fact, field, minval, maxval)
select ''' + @tempfact + N''', ''' + @tempfield + N''', min(' + @tempfield+ N'), max(' + @tempfield + N')
from ' + @tempfact
exec sp_executesql @sqlstr
fetch next from factfield into @tempfact, @tempfield
end
select * from #minmax
--drop table #minmax
close factfield
deallocate factfield
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply