Single-column user defined variable based on a comparison

Question

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") <= 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") >= highest_threshold) as max_error,

What I’m trying for is something like:

Case when (select(actual_error * "constant") <= 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.

• danblack: SHOW CREATE TABLE {x} for the table structure, some sample data, and expected output. You description is quite hard to follow.
• Akina: each row is a separate timed event. Does this mean that there exists some timestamp field in table's structure, and it is unique?

Answer 1

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 < 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 >= t2.lower_bound
 AND t1.actual_error < t2.upper_bound
/* or alternatively, dependent by a real logic
WHERE t1.actual_error > t2.lower_bound
 AND t1.actual_error <= 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.

Answer 2

Your attempt is inside out:

SELECT
 Case when actual_error * "constant" <= lowest_threshold THEN "level_1_error"
 when actual_error * "constant" between this and that THEN "level_2_error"
 ...
 END AS Level
 FROM ...;