Alias not working on IF condition in MYSQL

Question

I am writing an mysql query and getting error.

"Unknown column 'dat' in 'field list'"

This error occurred due to use alias in IF condition in mysql.

Here is mysql query :

SELECT 
    nCustomerID,
    dDateRegistered,
    (select count(nPlayerID) from credit_logs 
        where nPlayerID=nCustomerID) as total_clog,
    (select count(nPlayerID) FROM bl_transaction_history
        where nPlayerID=nCustomerID) as total_tran,
    (select count(nCustomerID) from customer_freeplays
        where nCustomerID=nCustomerID) as total_free,
    (select dDateAdded from bl_transaction_history
        where nPlayerID=nCustomerID) as dat,
    (select DATEDIFF(now(),dat)/30 ) as date_differece1,
    (select DATEDIFF(now(),dDateRegistered)/30 ) as date_difference2,
    IF (dat IS NOT NULL,(select DATEDIFF(now(),dat)/30 ),
        (select DATEDIFF(now(),dDateRegistered)/30 )) as date_difference
FROM bl_customers
WHERE nAccountStatus=1 
  and bDeleted=0 
having total_clog>0 
    or total_tran>0 
    or total_free>0

Any help would be appriciated.. :)

Thanks in advance.

The `where nCustomerID=nCustomerID` needs correction probably. — ypercubeᵀᴹ, May 05 '12 at 14:37

score 6 · Accepted Answer · answered May 05 '12 at 14:22

6

You can't use alias columns, within your selection of other columns. You'll need to copy the entire part of the query you're aliasing, over and over again. ie. replace all occurrences of dat, after your initial declaration, with (select dDateAdded from bl_transaction_history where nPlayerID=nCustomerID)

answered May 05 '12 at 14:22

Bryan

6,682
2
17
21

@Sanjay: You can't use a column alias in another column calculation or in the `WHERE` clause. See my answer here: [Using 'case expression column' in where clause](http://stackoverflow.com/questions/6545664/using-case-expression-column-in-where-clause/6545685#6545685) for another workaround. – ypercubeᵀᴹ May 05 '12 at 14:35

Alias not working on IF condition in MYSQL

1 Answers1