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

[Vixremento]

Howdy!

Okay I have a bit of a problem - I can't use case statements because the statements that my app generates produces nested case statements (for each formula) that quickly exceed the maximum number allowed by SQL Server.

Now my question is this - is it possible to accomplish something like this without using case statements at all in SQL?

select case when (case when 1 = 1 then 1 else 0 end) + (case when 2 = 2 then 2 else 0 end) = 3 then 5 else 0 end

 

[ToxicBunny]

What exactly are you trying to achieve?

 

[Vixremento]

My app generates SQL statements that are based on more user-friendly expressions applied to an ADO.NET datatable - a problem with this though is that the amount of data I'm pushing through (together with the number of expressions) gets me a System.OutOfMemory exception pretty quickly so I decided to offload the work onto SQL in a temp table instead of putting too much data into the datatable...but that means that I have to build SQL update statements on the fly that do the same job. I did this and it worked well (using CASE statements) except now the number of expressions has grown to a point where the nesting breaks it.

Here's an example of what I'm doing - there user adds one or more expressions (which in turn can use other expressions) - something like this:

Expression1 = IIF(SomeValue1 = 1, 5, 0)
Expression2 = IIF(SomeValue2 = 2, 10, 0)
Expression3 = IIF(Expression1 = 5, Expression2, Expression1)

Now I create a temp table adding the input values. After that I add columns for the expressions and after that I execute update statements (the expressions) to update the "formula" fields.

My SQL version of the expressions looks something like this:

UPDATE #Temp SET [Expression1] = CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END
UPDATE #Temp SET [Expression2] = CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END
UPDATE #Temp SET [Expression3] = CASE WHEN [Expression1] = 5 THEN [Expression2] ELSE [EXPRESSION1] END

The problem with Expression3 is that it needs the values for Expression1 and Expression2 to already have been calculated - so to make sure I replace Expression1 and Expression2 with the full SQL version - like this:

UPDATE #Temp SET [Expression3] = CASE WHEN (CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END) = 5 THEN (CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END) ELSE (CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END) END

 

[ToxicBunny]

Yeah... a simple select statement doesn't necessarily gives us the best idea of the underlying data structure, or your required end results....

UDFs or sub queries could be better... or you could offload the actual calculation into a CLR if its feasible.... there are numerous options depending on your inputs and required outcome.

 

[ToxicBunny]

If I'm to be brutally honest..

What you're trying to do sounds like it SHOULD be done outside of the database engine before you start... it will be more efficient if you ask me.

 

[Vixremento]

Here's a full sample bit of SQL that you can use to see what I'm trying to do with the expressions. With the user being able to add expressions as they like (these are then applied as a batch process to massive amounts of data) it would be nice to somehow let the server do the work for me in SQL. Unfortunately we're restricted to SQL 2000 and we're only allowed to target the .NET 2.0 framework.

CREATE TABLE #Temp(SomeValue1 INT, SomeValue2 INT, SomeValue3 INT)
ALTER TABLE #Temp ADD Expression1 INT
ALTER TABLE #Temp ADD Expression2 INT
ALTER TABLE #Temp ADD Expression3 INT

INSERT INTO #Temp (SomeValue1, SomeValue2, SomeValue3) VALUES (1, 2, 3)

UPDATE #Temp SET [Expression1] = CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END
UPDATE #Temp SET [Expression2] = CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END
--Can't assume that Expression1 and Expression2 will have been updated yet...so this UPDATE isn't quite valid
----UPDATE #Temp SET [Expression3] = CASE WHEN [Expression1] = 5 THEN [Expression2] ELSE [EXPRESSION1] END
--This one replaces the other expressions with the relevant SQL
UPDATE #Temp SET [Expression3] = CASE WHEN (CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END) = 5 THEN (CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END) ELSE (CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END) END

 

[ToxicBunny]

This all just sounds completely arse about face to me...

you're getting sql to do dog work for you on values you input.... you should do that work outside SQL THEN pump it into sql... especially if you're being somewhat dynamic about it due to SQL not being able to store an optimal plan for that kind of work since it will more than likely constantly change.

There also seems to be a fairly good possibility of doing dodgy work on actual data with your queries being setup the way you're setting them up.

 

[bekdik]

Can't this be done by using select statements?

 

[Vixremento]

This all just sounds completely arse about face to me...

you're getting sql to do dog work for you on values you input.... you should do that work outside SQL THEN pump it into sql... especially if you're being somewhat dynamic about it due to SQL not being able to store an optimal plan for that kind of work since it will more than likely constantly change.

There also seems to be a fairly good possibility of doing dodgy work on actual data with your queries being setup the way you're setting them up.

I originally tried using datatables in an assembly to do this work but loading the amount of data we have is rather painful and it fell over with out of memory issues. We first have to read all of the data from the database and put it into some kind of structure, perform the calculations and then save the results back to the database). I thought using SQL for this purpose would be better because then there is reduced network activity taking place...I'm not sure there really is a more efficient way of doing this without creating our own evaluation engine to process the formulas (one that is more memory efficient that the ADO.NET datatables).

 

[ToxicBunny]

I would investigate using a service or a CLR for the job rather than SQL itself....

it kind of sounds like you're trying to mangle SQL into doing a job it wasn't designed for.

 

[ToxicBunny]

Actually looking at nfbs's idea... there is possibility in it... depending on what type of datastorage you have.. you could probably use a persistent table to do some kind of auditing on the type of work your users do with the data.

 

[ToxicBunny]

Oi... deleting your post then posting it again.... *shocked*

 

[nfbs]

I just realized he posted the solution to his own problem so wondered what was the point of the post but reposted again.

 

[Vixremento]

I can't really assume that Expression1 and Expression2 would have been calculated before Expression3. I think that one way of reducing the load is to build code that prioritizes the expressions to calculate them in order - if this is done then I will be able to just use your suggestion - and hopefully reduce the number of CASE statements generated to a max of one condition per expression.

 

[nfbs]

when you say "you can't assume that Expression1 and Expression2 will have been updated yet...so this UPDATE isn't quite valid"
if you have 3 update statements and run at the same time it will update the table in one shot by locking the table it has to lock the table in order to know that what to roll back.

So
UPDATE..
UPDATE..
UPDATE..
Go

OR


BEGIN TRANSACTION;
GO
UPDATE ..
UPDATE ..
UPDATE ..
GO
COMMIT TRANSACTION;
GO

In either case if one update fails it rollbacks all changes.

 

[Vixremento]

when you say "you can't assume that Expression1 and Expression2 will have been updated yet...so this UPDATE isn't quite valid"
if you have 3 update statements and run at the same time it will update the table in one shot by locking the table it has to lock the table in order to know that what to roll back.

So
UPDATE..
UPDATE..
UPDATE..
Go

OR


BEGIN TRANSACTION;
GO
UPDATE ..
UPDATE ..
UPDATE ..
GO
COMMIT TRANSACTION;
GO

In either case if one update fails it rollbacks all changes.

Well what I'm saying is that I don't know what order the fields will be updated in. If I execute this as a batch but in a different order then the value for Expression3 would have been based on what the value for Expression1 and Expression2 were before the 2nd and 3rd update statements:

UPDATE #Temp SET [Expression3] = CASE WHEN [Expression1] = 5 THEN [Expression2] ELSE [EXPRESSION1] END
UPDATE #Temp SET [Expression1] = CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END
UPDATE #Temp SET [Expression2] = CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END

 

[ToxicBunny]

Sounds like you need to implement some kind of UI validation.... if you can't assume certain conditions then it makes your coding considerably MORE difficult.

 

[Vixremento]

Sounds like you need to implement some kind of UI validation.... if you can't assume certain conditions then it makes your coding considerably MORE difficult.

Well the nice thing is that I can use a datatable to validate the expressions - problem is that the order in which the column results are calculated is unknown if I use SQL (i.e. the same way a computed column in sql doesn't allow you to use another computed column in it's expression).

If you had the following inputs:
A = 1
B = 2
C = 3

and then allowed the user to write formulas around these like this:
MyFuzzyThing = A + B
YourFuzzyThing = C + A - MyFuzzyThing
HisFuzzyThing = YellowBunny + IIF(GreenBunny = 'Alive', 'Kill', 'Bury')
YellowBunny = 'Dead'
GreenBunny = ' green bunny'

Then I would need to make sure that YellowBunny and GreenBunny are set before updating HisFuzzyThing to get a value of "Dead green bunny".

So what I'll do is first update values that don't include other expressions (i.e. they only use inputs) - then process those that do include expressions provided that I have already updated that expression.

 

[nfbs]

UPDATE #Temp SET [Expression1] = CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END
UPDATE #Temp SET [Expression2] = CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END
UPDATE #Temp SET [Expression3] = CASE WHEN [Expression1] = 5 THEN [Expression2] ELSE [EXPRESSION1] END

Your script is written in that order so SQL will not change the order and will not start the next update if the previous one fails or is not finished and will do them sequentially.

You can also run them seperately but this won't rollback all the updates if one update fails.

UPDATE #Temp SET [Expression1] = CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END
Go
UPDATE #Temp SET [Expression2] = CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END
GO
UPDATE #Temp SET [Expression3] = CASE WHEN [Expression1] = 5 THEN [Expression2] ELSE [EXPRESSION1] END
GO

 

[Vixremento]

UPDATE #Temp SET [Expression1] = CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END
UPDATE #Temp SET [Expression2] = CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END
UPDATE #Temp SET [Expression3] = CASE WHEN [Expression1] = 5 THEN [Expression2] ELSE [EXPRESSION1] END

Your script is written in that order so SQL will not change the order and will not start the next update if the previous one fails or is not finished and will do them sequentially.

You can also run them seperately but this won't rollback if one update fails.
UPDATE #Temp SET [Expression1] = CASE WHEN [SomeValue1] = 1 THEN 5 ELSE 0 END
Go
UPDATE #Temp SET [Expression2] = CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END
GO
UPDATE #Temp SET [Expression3] = CASE WHEN [Expression1] = 5 THEN [Expression2] ELSE [EXPRESSION1] END
GO

Assume now that the user changes Expression1 to "Expression2 * 2". We would then have this as the update statements:
UPDATE #Temp SET [Expression1] = [Expression2] * 2
UPDATE #Temp SET [Expression2] = CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END
UPDATE #Temp SET [Expression3] = CASE WHEN [Expression1] = 5 THEN [Expression2] ELSE [EXPRESSION1] END

Now by default the expression columns are added without values (i.e. they're null) but I update them after adding them initially to set them to zero. Assuming that the values came from this insert statement:
INSERT INTO #Temp (SomeValue1, SomeValue2, SomeValue3) VALUES (1, 2, 3)

the output values would be:
Expression1 = 0 * 2 = 0
Expression2 = 10
Expression3 = 0

I would have to make sure that Expression2 was updated first, then Expression1 and then Expression3 - in this order:
UPDATE #Temp SET [Expression2] = CASE WHEN [SomeValue2] = 2 THEN 10 ELSE 0 END
UPDATE #Temp SET [Expression1] = [Expression2] * 2
UPDATE #Temp SET [Expression3] = CASE WHEN [Expression1] = 5 THEN [Expression2] ELSE [EXPRESSION1] END

to make sure that the output values are properly set to be:
Expression2 = 10
Expression1 = 10 * 2 = 20
Expression3 = 20