WHERE clause limitations

The sysams system catalog table has no indicator to inform the database server that a secondary access method cannot process complex qualifications. If the access method does not process the Boolean operators in a WHERE clause, perform the following actions:
  • Provide examples in the user guide of UNION and subqueries that replace AND or OR operators in a WHERE clause, as the following example demonstrates.
    Query using boolean operator Query using UNION 
    SELECT * FROM videos 
  WHERE title = 'Hamlet'
         OR year > 1980;
    		 
    SELECT * FROM videos WHERE title = 'Hamlet
UNION
SELECT * FROM videos WHERE year > 1980;
  • In the am_scancost purpose function, call the mi_qual_issimple() or mi_qual_boolop() accessor function to detect a Boolean operator.

    If mi_qual_issimple() returns MI_FALSE, for example, return a value that forces the optimizer to ignore this access method for the particular query.

  • Raise an error if mi_qual_issimple() returns MI_FALSE to the am_getnext purpose function.