Mysql select where procedure=1

Asked Nov 03 '19 at 09:17

Active Nov 03 '19 at 09:17

Viewed 28 times

I have procedure "CheckStatus" which returns 0 or 1. How I can use this query without HAVING? It's not work properly with limit 0,10 - it filtering rows with status=0 and I never got 10 results as I expected. Yes I understand that HAVING w/o GROUP BY just filtering results and not rows.

How I can use status in WHERE?

select id,CheckStatus(stock,delivery_days) as `status` from new where 
id in ('52078','52093','52095','81584','81589') 
having status in ('1') 
order by field (id,'52078','52093','52095','81584','81589') group by id limit 0,10

Also maybe I can someway create generated column with this procedure? I tried to add it but got an error about I can't call procedure for generated columns.

asked Nov 03 '19 at 09:17

rst630

1

You can't refer to `status` in the `WHERE` clause. You either have to use your version or subquery. – Tim Biegeleisen Nov 03 '19 at 09:20
2

I think you can replace `having status in ('1')` by `where CheckStatus(stock,delivery_days) in ('1')` – Luuk Nov 03 '19 at 09:28
1

CheckStatus is a function not a procedure.. – P.Salmon Nov 03 '19 at 09:29
btw `orderby` comes next to `group by` so this will throw error. – James Nov 03 '19 at 09:30
A group by without any aggregate functions is rarely (if ever) useful. – P.Salmon Nov 03 '19 at 09:37

Mysql select where procedure=1

0 Answers0