SQL CASE - Case expressions may only be nested to level 10

[ToxicBunny]

Ok let me rephrase that since it sounds exactly like what I said earlier

I think you need to rethink HOW you are going about this.. It almost sounds as if a single solution isn't the answer.. its almost as if you need to re-engineer the process so you can processing inside and outside of SQL.. especially if you're overloading datatables in ADO.NET

 

[nfbs]

So if I understand you correct your "somevalues" column themselves can be dynamic ie. be formulas and are built up from the front end.
Then sql is not the tool.
You need to calculate the formulas in .net code (not through sql case statements) row by row and not using a batch sql statements.
So put all the data in .net code (collection of class representing the data/datatable with the 3 expr fields as properties/columns)
Iterate through the collection/datatable using .net code to calculate the expression values a row at a time.
Save the end results back to the db.

 

[Vixremento]

Ok let me rephrase that since it sounds exactly like what I said earlier

I think you need to rethink HOW you are going about this.. It almost sounds as if a single solution isn't the answer.. its almost as if you need to re-engineer the process so you can processing inside and outside of SQL.. especially if you're overloading datatables in ADO.NET

I agree with you - it would be better for us to create our own engine using more optimised data structures (the DataTables seem to gobble memory)...sadly we just don't have the luxury of time on our side to do that. I was just hoping to find a way to use the IF statement or something similar in SQL without having to use CASE statements but I think my problem can't be solved as easily as that. It's a pity though...because working with set based operations to huge chunks of data is exactly what SQL was built for - there is just no way that we're going to match it's performance ourselves very easily even if we did have a few more months to work on this.

Thanks for the replies though - I think I know what needs to be done...now it's just a matter of coding it!

 

[ToxicBunny]

Yeah without a doubt set based operations are exactly what SQL is built for... and it does it BLOODY well...

your biggest problem is your inputs are not of a fixed format... you need to seperate the SQL work from calculating the update equations...