(v.1)
SELECT substring(payment_id,1,1) as v1,
IF(substring(payment_id,1,1) < 5, 'P', '---') as info
FROM payment
ORDER BY v1;
In the query above I am trying to use variable 'v1'. In this case getting the first character from "payment_id" is crucial for some information. Query (v.1) works correctly, but "substring(payment_id,1,1)" in "IF" condition seems to be some kind of unnecessary duplication.
To avoid duplication and simplify the query I modified it a bit:
(v.2)
SELECT substring(payment_id,1,1) as v1,
IF(v1 < 5, 'P', '---') as info
FROM payment
ORDER BY v1;
but in this case "IF" doesn't work. I wonder why I can use v1 in ORDER BY but not in IF condition.
The ideal solution (in case of simplicity) in this simple case could be:
(v.3)
#declaring of some variables earlier (before SELECT; it doesn't work):
SET @v1 = substring(payment_id,1,1);
#then just use them (query as simple as possible):
SELECT v1,
IF(v1 < 5, 'P', '---') as info
FROM payment
ORDER BY v1;
Maybe I'm trying to simplify the query too much.
Is it even possible to do it as I propose in version 3?
I'm wondering what is the optimal approach when I have several variables (calculated based on data from certain table columns) and then using them in IF-ELSE conditions.
