CLR integration and complex column processing

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

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

CLR integration and complex column processing

Author

Topic

coolaqua
Starting Member

1 Post

Posted - 2007-10-18 : 20:37:33

Hi,

I have to implement a complex algorithm that processes each row and each column.I have bunch complex steps at the end of which a table gets populated with the processed columns.

My question is whether it is possible and feasible to do this kind of processing using CLR integration or should I stick to T-SQL ?

One of the steps of processing involved per column is as follows:-
1)For each column,find successive invalid values from the start of the column.Invalid value= any value <0
2)find the invalid value depth of each column(no of successive invalid values from start)
3)If after these invalid vlaues,there is a valid value and another invalid value,replace current invalid value with valid value.
ie replace invalid value only if it has a valid value above it.
4)find the column with maximum invalue value depth and delete that many rows from the table.

Here's an example:-
Suppose there are 2 columsn colA and ColB.The columns have different datatypes eg decimal,int,string etc.
For simplicity colA and colB are ints.
RowID is keeping track of the row number.

suppose colA has the following data

RowID ColA
-----------
1 0
2 -5
3 -3
4 1
5 4
6 -9
7 5
8 8

Step1)successive invalid values from start=0,-5,-3
Step2)Invalid value depth=3(because there are 3 rows from step 1)
Step3)0,-5,-3 do not have any valid value above them.But -9 has a valid value 4 above it.So replace -9 with 4.

so colA after the algo will look as follows
RowID ColA
------------
1 0
2 -5
3 -3
4 1
5 4
6 4(replaced -9 with 4)
7 5
8 8

Now do the next column colB
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 -5
8 -8

Step1)successive invalid values from start=-6,0,0,-7
Step2)depth of invalid values=4
Step3)Next invalid value -5 occurs at RowID 7
and has a valid value 8 above it.
Replace -5 with previous valid vlaue ie 8.

RowID 8 has invalid value -8 .Its previous invalid value(-5) got replaced by a valid value 8.So replace RowID8 also with value of RowID 7 ie 8

Output at the end of these steps
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 8(replaced -5 with 8)
8 8(replaced -8 with 8)

Step4:Get the maximum invalid value depth
In this case ColB had depth=4 which is greater than ColA which had dept=3.
So delete 4 rows from the beginning of the table
So the output will be

RowID colA colB
----------------------------------------
5 4 4
6 4(replaced -9 with 4) 8
7 5 8 (replaced -5 with 8)
8 8 8(replaced -8 with 8)

Thanks in advance for your help

coolaqua

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 10:58:22

if you have a set number of columns you can try the below repeated for each column and changing the column name used. if the number of columns is expected to grow, or there are MANY, you can turn this into dynamic sql based on the INFORMATION_SCHEMA.COLUMNS view. good luck...

declare @sample table (rowid int, cola int, colb int)

insert @sample
select 1,0,-6
union all
select 2,-5,0
union all
select 3,-3,0
union all
select 4,1,-7
union all
select 5,4,4
union all
select 6,-9,8
union all
select 7,5,-5
union all
select 8,0,-8
--colA
update sample
set cola = validrow.cola
from @sample as sample
join
(
select invalid.rowid, MAX(previously_valid.rowid) as validrowid
from @sample as invalid
join @sample as previously_valid
on previously_valid.rowid < invalid.rowid
and previously_valid.cola > 0
where invalid.cola <= 0
group by invalid.rowid
) as correction
on sample.rowid = correction.rowid
join @sample as validrow
on validrow.rowid = correction.validrowid

delete from @sample where cola <= 0
--colB
update sample
set colb = validrow.colb
from @sample as sample
join
(
select invalid.rowid, MAX(previously_valid.rowid) as validrowid
from @sample as invalid
join @sample as previously_valid
on previously_valid.rowid < invalid.rowid
and previously_valid.colb > 0
where invalid.colb <= 0
group by invalid.rowid
) as correction
on sample.rowid = correction.rowid
join @sample as validrow
on validrow.rowid = correction.validrowid

delete from @sample where colb <= 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources