Small Cookbook

Database Structure

MySQL supports text columns as part of a WHERE clause using equality while Oracle for instance does not. This means that if you need to do something like that:

SELECT * FROM tx_ext WHERE column = 'something'

Make sure not to use text as column type but instead use varchar(4000) which is the limit for Oracle. The other solution is to use a LIKE operator:

SELECT * FROM tx_ext WHERE column LIKE 'something'

WHERE Clauses

The SQL parser is not as powerful as it could be. Typical problems occur with calculated conditions such as

... WHERE column1 + number1 >= number2

Rules of thumb

  1. The calculated part (column1 + number1) must start with a column name
  2. The calculated part may have a column name or a number as second operand
  3. The part after the operator (number2) must be a number
  4. The calculated part can only occur on the left hand of the comparison operator
  5. More than two operands on the left hand are not supported