0

For example, I'm defining this variable:

@test:= CASE WHEN @age < 21 THEN 'young' WHEN @age > 21 THEN 'old'

Fairly simple and straightforward. This would work when i do it in the SELECT clause, but somehow when i do it in the WHERE clause it gives 0 results, while i can define other variables like @test:= 1 without problems (returning the 10k+ results as expected).

I also tried it in multiple ways but it just doesn't seem to work with the case..when..then situation. Am i missing something or is this just not possible?

EDIT: Not the actual query, but an example of what i mean

SELECT 
    id, @test
FROM
    ratetypes
WHERE
    @test:=CASE
        WHEN ratetype_id IN (1 , 2, 7, 9) THEN 'numeric'
        ELSE 'non numeric'
    END
bibimoebaba
  • 9
  • 2
  • Can you include your actual query in the question – Stu Jul 13 '22 at 09:31
  • @Stu Added something that's alike, the original query is 55 lines long but this should demonstrate what i mean enough i think. – bibimoebaba Jul 13 '22 at 09:45
  • does [this](https://stackoverflow.com/questions/40483072/mysql-assign-user-defined-variable-in-where-clause) answer your question? – Asgar Jul 13 '22 at 09:53
  • @Asgar Not really. 'Normal' variables work fine, for example ```@test:= 1``` works like a charm, same with calculated values but it just does not seem to work with case..when..then. – bibimoebaba Jul 13 '22 at 10:03
  • Your query wouldn't work as you need. In SQL the query execution steps ordering may not match the SQL text. In your particular case the output rowset creation will be performed after all WHERE evaluations, and `@test` in all output rows will be the same and equal either NULL (which will result in none rows returned - most likely) or the variable value assigned into before the query or the evaluation result for the row evaluated last (all rows will be returned - least probability). The variant depends on does the variable was assigned to some definite value before the query. – Akina Jul 13 '22 at 10:09
  • I understand what you're saying @Akina, but i don't get why the same thing would work in a SELECT statement but not in a WHERE clause. Also, shouldn't it just take the ELSE as a result when the items don't match, meaning it will be 'non numeric'. My question is why this would work in the SELECT clause, but not in the WHERE clause, while it works with other variables in the WHERE clause. – bibimoebaba Jul 13 '22 at 11:34
  • *i don't get why the same thing would work in a SELECT statement but not in a WHERE clause.* WHERE is processed separately. Then a lot of intermediate steps is performed. And only after this the output values are evaluated. When the output rowset starts its iterations all WHERE iterations are already processed and all variable evaluations are already performed. – Akina Jul 13 '22 at 11:54
  • You may try to work with your variable not in WHERE but in HAVING. Maybe this will result in the output which you need (but I doubt nevertheless). – Akina Jul 13 '22 at 11:55
  • Maybe i'm not understanding what you mean but i still don't get why i can assign values to the variables in the WHERE clause, like the examples i gave, but not use the case..when..then for it. – bibimoebaba Jul 13 '22 at 12:48

0 Answers0