in SQL, supposes I need to refer to an aliased field in the having clause but the alias has quotes, how do I do that?
select (select...) as '005'
group by ...
having '005'>0
+3
A:
I think you are missing a FROM clause, and you should use backticks instead of single quotes:
SELECT (SELECT ...) AS `005`
FROM table1
GROUP BY ...
HAVING `005` > 0
It would help if you posted your full query, as I am guessing a bit here as to what you want to do.
Mark Byers
2010-06-05 00:03:16
As Mark said the problem is that you are using single quotes rather than back ticks, which makes the alias be treated as a string literal. In the select portion the alias can use normal quotes, but anywhere else it is used you need to use the back ticks.
Waleed Al-Balooshi
2010-06-05 00:18:58
You guys are right, if I use Backticks instead of single quotes the alias works!, thanks
2010-06-05 00:24:13
+1
A:
The SQL-92 standard defines using double quotes for a column alias, rather than single quotes. In most databases, unusual characters are only allowed if you use double quotes.
That said, not all databases support referring to a column alias (in the same query) in the GROUP BY or HAVING clauses. For portable queries, I don't recommend the practice of referring to a column alias in the GROUP BY or HAVING clauses. Additionally, the HAVING clause is for aggregates - the simplified example you provided should trigger an error because no aggregate function (IE: COUNT, AVG, MIN/MAX, etc) is performed on the 005 column alias.
The following works for me on MySQL 4.1:
SELECT COUNT(*) AS "005"
FROM TABLE t
GROUP BY ...
HAVING `005` > 0
OMG Ponies
2010-06-05 00:13:14