Single-column user defined variable based on a comparison I have a database with over 300k lines and each row is a separate timed event.  I have a column of system spec values for error, followed by a stochastic draw on an actual error for that event.  I can change the confidence level for the actual error by multiplying the actual error by a constant.

I have tried this query and while it returns a 1 in each error code, what I am looking for is a way to develop a user defined variable called “myerror” that is filled by each rows error * the constant.

sample query statements:

((select(actual_error * “constant”) &lt;= lowest_threshold) as level_1_error,
((select(actual_error * “constant”)  between this and that) as level_2_error,
((select(actual_error * “constant”)  between higher_this and higher_that) as level_3_error,
…
…
…
((select(actual_error * “constant”) &gt;= highest_threshold) as max_error,


What I’m trying for is something like:

Case when (select(actual_error * “constant”) &lt;= lowest_threshold  set @user_error  =  “level_1_error”
          when (select(actual_error * “constant”)  between this and that) set @user_error  =  “level_2_error”


but, alas…..can’t seem to get it to work…..I’ve tried putting the set statement in a sub-query and just using a sub-query to select a different error level also…..

help bitte sehr.
I'd recommend you to create static, temporary or dynamic table which contains the adjacent border ranges for each error level normalized with “constant” value to avoid additional calculations (schema):



CREATE TABLE error_level_ranges ( level, lower_bound, upper_bound )
AS 
SELECT 1,-1,2     -- 2 as "lowest_threshold"/constant,
                  -- assume -1 is below min possible value of actual_error
UNION ALL
SELECT 2,2,10     -- 10 as "that"/constans, assume "this"="lowest_threshold"
UNION ALL
SELECT 3,10,25    -- 25 as "higher_that"/constant, assume "higher_this"="that"
-- ... 
UNION ALL
SELECT 99,100,999 -- 100 as "highest_threshold"/constant, assume levels count &lt; 99
                  -- and 999 is over max possible value of actual_error


and then

SELECT t1.*, t2.level
FROM error_events t1, error_level_ranges t2
WHERE t1.actual_error &gt;= t2.lower_bound
  AND t1.actual_error &lt; t2.upper_bound
/* or alternatively, dependent by a real logic
WHERE t1.actual_error &gt; t2.lower_bound
  AND t1.actual_error &lt;= t2.upper_bound */


If you have a lot of error categorization schemes with different level counts and their borders, you must add a field (named similar to categorizing_scheme) into error_level_ranges table and proper additional expression like AND t2.categorizing_scheme=@current_scheme to conditions section.