On 15/04/2004 11:25 Dan Field wrote:
I've stumbled across a query I don't quite understand the error message for.
This query is pulled from a working MySQL setup:
SELECT
DEWEY_ID, DEWEY_HUNDREDS, DEWEY_TENS, DEWEY_ONES, DEWEY_POINT_ONES, DEWEY_POINT_TENS, DEWEY_POINT_HUNDREDS, DEWEY_POINT_THOUSANDS, DEWEY_TYPE, DEWEY_LANG, DEWEY_SUBJECT FROM lu_dewey
WHERE
(DEWEY_HUNDREDS = 9) AND
(DEWEY_TENS >= 0) AND (DEWEY_TENS <= 9) AND
(DEWEY_ONES = 0 || DEWEY_ONES = NULL) AND
(DEWEY_POINT_ONES = 0 || DEWEY_POINT_ONES = NULL) AND
(DEWEY_POINT_TENS = 0 || DEWEY_POINT_TENS = NULL) AND
(DEWEY_POINT_HUNDREDS = 0 || DEWEY_POINT_HUNDREDS = NULL) AND
(DEWEY_POINT_THOUSANDS = 0 || DEWEY_POINT_THOUSANDS = NULL) AND
(DEWEY_TYPE = 't') AND
(DEWEY_LANG = 'en')
ORDER BY DEWEY_TENS
However I'm getting the following error:
ERROR: Unable to identify an operator '=' for types 'character' and 'boolean' You will have to retype this query using an explicit cast.
Any help would be much appreciated
You're trying to use the string concatenation operator (||) in a boolean test.. That's an invalid comparison according to the SQL specs. You need to use the SQL OR operator e.e.,
(DEWEY_POINT_TENS = 0 OR DEWEY_POINT_TENS = NULL) AND
~~Your "= NULL" tests are also not valid SQL (should be IS NULL). MySQL does not follow the specs in a number or areas. PostgreSQL is very standards-complient. Write valid SQL and you should be OK.
HTH
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for Business |
| Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
