Using user-defined variable from select statement in its where clause

Question

I have this select statement working fine:

SELECT 
  pre, dict.char, post, score
  , (SELECT @res := MatchPre(pre, 'test') AS var) AS mpre 
FROM dict;

It returns all of the data properly, with the result from MatchPre in a column labeled "mpre". However, I want to only get results where mpre > 0. So, logically, I tried this:

SELECT 
  pre, dict.char, post, score
  , (SELECT @res := MatchPre(pre, 'test') AS var) AS mpre 
FROM dict 
WHERE mpre > 0;

But to no avail. I've tried swapping "WHERE mpre > 0" with "WHERE @res > 0", "WHERE @res.mpre > 0", "WHERE mpre.@res > 0", etc, but it either throws an error or returns an empty result set despite the original where-less query giving rows with mpre values greater than 0.

What is the correct syntax for this?

Johan · Accepted Answer · 2011-10-25 19:32:57Z

6

SELECT 
  pre, dict.char, post, score
  , (SELECT @res := MatchPre(pre, 'test') AS var) AS mpre 
FROM dict 
HAVING mpre > 0;

You cannot use aliases in a where clause.
You will have to use a having clause or put the select with the alias inside another outer select.

This is because when the where clause is run, the alias is not yet evaluated.
The having run after all the select,where, group by, from and joins are finished and does not suffer this limitation.
Having cannot use an index though.
If you want to use an index use a sub-select.

SELECT sub.* FROM
  (SELECT 
     pre, dict.char, post, score
     , (SELECT @res := MatchPre(pre, 'test') AS var) AS mpre 
   FROM dict ) sub
 WHERE sub.mpre > 0

answered Oct 25, 2011 at 19:32

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using user-defined variable from select statement in its where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related