Cant use filter where to virtual colmn in select MYSQL

Question

I used this query with virtual column New_Users

SELECT `r`.`deal_id` as Deal_ID, 
       concat("2013\-05\-15") as start_date, 
       concat("2013\-05\-16") as end_date, 
       abs(0) AS New_Members 
  FROM (`ws_deal` r) 
 WHERE New_Members != 0 
 ORDER BY `deal_id`"

I had a error "1 Unknown column 'New_Members' in 'where clause' SQL.sql 1 118 "

If i didn't use New_Members != 0 and if query is

 SELECT `r`.`deal_id` as Deal_ID, 
        concat("2013\-05\-15") as start_date, 
        concat("2013\-05\-16") as end_date, 
        abs(0) AS New_Members 
   FROM (`ws_deal` r)  
  ORDER BY `deal_id` 
  LIMIT 12"

I get a result .

Deal_ID start_date      end_date       New_Members  

407 2013-05-15  2013-05-16  0
408 2013-05-15  2013-05-16  0
409 2013-05-15  2013-05-16  0
410 2013-05-15  2013-05-16  0
411 2013-05-15  2013-05-16  0
412 2013-05-15  2013-05-16  0
413 2013-05-15  2013-05-16  0
414 2013-05-15  2013-05-16  0
415 2013-05-15  2013-05-16  0
416 2013-05-15  2013-05-16  0
417 2013-05-15  2013-05-16  0

My Question is why I can't filter this result . How can I filter this . (You may think anyway New_Member != 0 and filter is not needed . But I need this to be happen by a filter while this is dynamically generated query in a big query set)

Thanks a lot

You can't use alias names in `where` clauses. Only in `having,group by,order by` clauses — juergen d, Sep 22 '13 at 08:37
Just a query: `abs(0)` will always give you value as `0`. Which means all the values in `New_Member` column will be `0`. And then applying filter as `New_Member != 0`, there will be no rows to return. So what are you trying to achieve ?? — heretolearn, Sep 22 '13 at 09:02

score 0 · Answer 1 · answered Sep 22 '13 at 08:57

0

You cannot use aliased columns in where clause, but you can use subquery and filter by new columns:

select
    Deal_ID, start_date, end_date, New_Members
from (
    select
        `r`.`deal_id` as Deal_ID, 
        concat("2013\-05\-15") as start_date, 
        concat("2013\-05\-16") as end_date, 
        abs(0) AS New_Members 
    from (`ws_deal` r) 
) as a
where New_Members != 0 
order by `deal_id`"

answered Sep 22 '13 at 08:57

Roman Pekar

107,110
28
195
197

1

Thanks all, studying you all comments i figureout the error . and made a fix . – Samitha Suranjan Dharmasiri Sep 22 '13 at 10:12
@SamithaSuranjanDharmasiri so your question should be closed? – Roman Pekar Sep 22 '13 at 12:30

score 0 · Answer 2 · answered Sep 22 '13 at 09:00

0

First of all, abs(0) is not making sense. It will always return 0.

Secondly, you cannot use an alias in a where clause. You have to query inside the query.

answered Sep 22 '13 at 09:00

Arjen

289
2
4
11

Cant use filter where to virtual colmn in select MYSQL

2 Answers2