CODEWITHSUNDEEP
mysqlsql
Andrew
Andrew

Reputation: 477

MySQL - what is wrong with this query?

I'm running this query and getting an error:

select mst_machine.MachineName, 
  FROM_UNIXTIME(trn_dacs.DateTime,'%Y-%m-%d %H:%i:%s') as dataTimeStamp,     
  trn_dacs.TargetWeight as dataValue, 
  trn_dacs.Weight 
FROM trn_dacs 
  INNER JOIN mst_machine ON trn_dacs.MachineNo = mst_machine.MachineNo 
where dataTimeStamp >= '1/31/2018 7:21:16 AM'

The error is:

1054 - Unknown column dataTimeStamp in 'where clause'

I'm not as familiar with MySQL as I am with SQL Server. I'm sure I've done this in SQL Server before.

Without the where clause, the query runs fine and the column header does show dataTimeStamp properly. (FYI).

Also - I know I can do this by substituting trn_dacs.DateTime for dataTimeStamp, however, I need to use an alias for that timestamp so that I can always auto-generate the where clause for any table structure.

Upvotes: 0

Views: 85

Answers (2)

Ike Walker
Ike Walker

Reputation: 65537

As mentioned in comments by others, you cannot use a column alias from the SELECT clause in the WHERE clause.

One hack you can use is to use a HAVING clause instead of a WHERE clause. This will not necessarily perform well (since MySQL cannot use an index to satisfy the filtering), but it satisfies your requirements as I understand them:

select mst_machine.MachineName, 
  FROM_UNIXTIME(trn_dacs.DateTime,'%Y-%m-%d %H:%i:%s') as dataTimeStamp,     
  trn_dacs.TargetWeight as dataValue, 
  trn_dacs.Weight 
FROM trn_dacs 
  INNER JOIN mst_machine ON trn_dacs.MachineNo = mst_machine.MachineNo 
HAVING dataTimeStamp >= '2018-01-31 07:21:16'

Another option is to use the column in the WHERE clause, and apply a function to the timestamp you use for filtering. This could perform better since MySQL can use an index (if you have one) for the filtering:

select mst_machine.MachineName, 
  FROM_UNIXTIME(trn_dacs.DateTime,'%Y-%m-%d %H:%i:%s') as dataTimeStamp,     
  trn_dacs.TargetWeight as dataValue, 
  trn_dacs.Weight 
FROM trn_dacs 
  INNER JOIN mst_machine ON trn_dacs.MachineNo = mst_machine.MachineNo 
WHERE trn_dacs.DateTime >= unix_timestamp('2018-01-31 07:21:16')

Upvotes: 0

Victor Cataraga
Victor Cataraga

Reputation: 103

You can't use aliases in the where clause, use:

WHERE FROM_UNIXTIME(trn_dacs.DateTime,'%Y-%m-%d %H:%i:%s') >= '1/31/2018 7:21:16 AM' instead of: where dataTimeStamp >= '1/31/2018 7:21:16 AM'

Upvotes: 2

Related Questions