Calculated result sets

P

pcf108

Active member
Joined
Sep 19, 2003
Messages
30
Im having a bit of design quandry... I need to return a result set of calculated values based upon multiple columns of data. Basically they are size calucations based on a table with Height, Width, and Thickness...


In T-SQL... (simplified, AdjustWidth involves CASE and gets complex)

Code: 
SELECT [INDENT]Height, 
Width, 
Thickness, 
AdjustWidth = Width - 1, 
AdjustThickness = Thickness + 4, 
TotalWidth = 2*Width + Thickness + 2
TotalTimes8 = 16*Width + 8*Thicknes + 16[/INDENT]
TotalWidth depends on "AdjustWidth" but it cannot be reffered to directly because is not a column. Id like to be able to do something like this, with a local variable

Code: 
SELECT [INDENT]Height, 
Width, 
Thickness, 
@AdjustWidth = Width - 1, 
@AdjustThickness = Thickness + 4, 
@TotalWidth = 2*@AdjustWidth + @AdjustThickness
TotalTimes8 = 8*TotalWidth[/INDENT]
But this is not allowed in T-SQL. And I would need to return the variables in the result set.

I could write the calculation all inline like the first example but it gets incredibly confusing and longer the deeper I get (mostly due to CASE statements). I could write the adjustments as functions but then calling the same functions over and over.

I could build out a DataSet programmatically, but then Id have some deployment issues. Any ideas on how to accomplish something like this on the back end?

Im thinking about attempting to do this with CLR in 2005... Thoughts?
 
Ive often put calculations in my SQL, if theyre simple or straightforward. If theres lots of CASE logic, its a toss-up - I may still keep it in SQL if it makes the most sense.

Two other options come to mind. First, expression columns in the dataset. Second, wrapping calls to your proc in a common C#/VB function that adds the columns programatically.

I might consider the expression columns first, because theyre a bit easier to setup. But expression columns do have issues - the biggest being I dont believe you can do any "if" logic in them, at least not in .NET 1.x - I havent looked into .NET 2.0.

The programatic solution seems the best in your case. What you describe is the need to get some base data out of the table, and from that base data get some new results, mostly calculated. That sounds like domain/business logic thats best put in the code thats easiest to understand and maintain.

-ner
 
Thats what I was thinking, its just that this result set would be used for reports, exporting, & ODBC connectivity to machinery. A SQL solution would likey fit the bill better... I keep thinking CLR in 2005 would do the trick, but i have zero experience, plus it kinda freaks me out, microsoft overload.

Ill take a look at the expression columns though, sounds intruiging.
 
If this is meant to offload, you may be able to push for a 24-hour stale version of the data. Meaning, youd build some kind of nightly batch job that calculated all your summary columns and put the results in a new table. Special reports (or data file extracts) would use this table to get the results. Clients would just have to agree that 24-hour stale data is Ok since youd only want to take this hit once a day. Ive implemented that kind of solution in the past. If your organization (or the client whos paying for the solution) can afford a stand alone reporting server that may even be better. That opens up a lot of possibilities, but comes at the cost of more hardware and possibly the complexity of a replication scheme. Just a few other ideas if you need a more data-centric approach where you wont be able to pull back data to some kind of C#/VB code for processing.

-ner
 
Well, Im not sure if that would work in this scenario... After somethought, I came up with a pretty simple T-SQL solution using a temp table and updates.

Putting the following in a stored procedure...
Code: 
SELECT *, AdjustWidth = Width - 1, AdjustThickness = Thickness + 4, TotalWidth = Width
INTO #sizeResult
FROM Orders

UPDATE #sizeResult SET TotalWidth = 2*AdjustWidth + AdjustThickness

SELECT * FROM #sizeResult

Im not sure that this method is efficient but it seemingly does the trick. The procedure could perform multiple selects into several temporary tables, but Im guessing an UPDATE strategy would work better for more calculations. I didnt check performance. In addition, it might be possible to add columns to the temp table...

From what I can tell CLR would work too (perhaps with better performance). Building a SqlMetaData result set and populating a DataSet, performing the calculations, and sending the MetaData through the SqlPipe.

So, it looks like I have a few real-time options. Since Im not working with 2005, Ill stick with T-SQL.
 
I think you could also have something like:

declare @AdjustWidth int, @AdjustThickness int, @TotalWidth int

select @AdjustWidth = Width - 1 from Orders
select @AdjustThickness = Thickness + 4 from Orders
select @TotalWidth = 2*@AdjustWidth + @AdjustThickness

select Height, Width, Thickness, @AdjustWidth, @AdjustThickness, @TotalWidth, 8*@TotalWidth as TotalTimes8
from Orders
 
Last edited by a moderator:
Back
Top