2 problems with getting correlation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

2 problems with getting correlation

Author

Topic

Roald
Starting Member

3 Posts

Posted - 2011-10-04 : 10:14:34

For a project i am trying to calculate the correlation from a number of columns in a table. There for i have written the function weightedcorrelation in C# and integrated it with sqlserver.

Now i have 2 problems:

1: The columns in the table are called v1 tm v99 but they dont all excist so i wanted to filter out the onces who dont so i dont get errors. But my IF statement doesnt seem to work.

2: when running i get the following error after the first 2 correlations when it finds an excisting table:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "WeightedCorrelation":
System.OverflowException: Arithmetic Overflow.
System.OverflowException:
at System.Data.SqlTypes.SqlDouble..ctor(Double value)
at System.Data.SqlTypes.SqlDouble.op_Implicit(Double x)
at WeightedCorrelation.Terminate()

I cant seem to find whats going wrong, do you guys have any hints of where to start looking?

Query:

DECLARE @int2 int
Set @int2 = 1
Declare @orgineletabel varchar(40)
set @orgineletabel = 'Tabel'
Declare @dbtabel varchar(40)
set @dbtabel = 'Correlatie'
Declare @collomnaam varchar(40)
set @collomnaam = 'v3'
Declare @collomnaam2 varchar (40)
set @collomnaam2 = 'v'+cast(@int2 AS VARCHAR(40))
Declare @database varchar(40)
set @database = 'database'

Begin
while (@int2 < 100)

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = +@orgineletabel AND COLUMN_NAME = +@collomnaam2)

Begin
PRINT +@collomnaam2+'doesnt excist!'
set @int2 = @int2 +1
Set @collomnaam2 = 'v'+cast(@int2 AS VARCHAR(40))
End

Else
Begin
Print +@collomnaam2+' excist'
Declare @statemant varchar(200)
set @statemant = 'insert into ['+@database+ '].[dbo].['+@dbtabel+'](Correlatie)
SELECT ['+@database+'].dbo.WeightedCorrelation('+@collomnaam+','+@collomnaam2+',1)
FROM ['+@database+'].[dbo].['+@orgineletabel+']
where '+@collomnaam2+' is not null'

execute(@statemant)

Set @int2 = @int2 + 1
Set @collomnaam2 = 'v'+cast(@int2 AS VARCHAR(40))
End
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-04 : 12:03:23

Can't tell from what you posted. But I'm guessing it is one of two things:
- you've exceeded the size of one of your C# objects/variables
- you've exceeded the overall memory limit of your custom CLR aggregate function. For instance, an AVG function is pretty light weight because all you need to store is the count and the sum. However, a MEDIAN function would require that you store all values so you can find the middle one(s). That Median function will violate whatever the size limit is for these CLR functions if there are enough values.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources