3

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.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Sanjay Khatri
  • 4,153
  • 7
  • 38
  • 42

1 Answers1

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)

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