Okay, I've done some research on the "Query is too complex error", and found out that it's usually caused in ADO when trying to update large numbers of fields at a time.
However, I'm just doing a select, not an update, and the SQL I'm using is going into a List Box's row source.
Here's what I'm doing:
I have a search form, where the user can enter in a search phrase.
This form contains a number of features:
- text box for search string, search button, and other stuff like that
- an option group(frmSearchType) to select if it is to search All Words(returns 1), Any Words(2), or Exact Phrase(3) (by the way, what is the proper naming convention for option groups?)
- an option group(frmAppearsIn) to select which fields we wish to select (Any Checked(1), All Checked(2), None Checked(3))
- 14 checkboxes, each one represents one of the possible searchable fields. The Tag value for each of these check boxes is set to the name of the field that is to be searched.
I have code that automatically generates the where statment for the sql, based on the input in the above form at the time that the submit button is clicked. The SELECT ... FROM ... is hard coded, as is the ORDER BY ...
the sql code that is generated looks like this:
(all 14 checkboxes are checked, searching for All words in Any Selected, search string is "foo bar")
SELECT ... FROM ... WHERE 0
OR ( 1
AND (nd.device_name LIKE 'foo' OR nd.device_name LIKE '*foo' OR nd.device_name LIKE 'foo*' OR nd.device_name LIKE '*foo*')
AND (nd.device_name LIKE 'bar' OR nd.device_name LIKE '*bar' OR nd.device_name LIKE 'bar*' OR nd.device_name LIKE '*bar*')
)
OR ( 1
AND (dt.name LIKE 'foo' OR dt.name LIKE '*foo' OR dt.name LIKE 'foo*' OR dt.name LIKE '*foo*')
AND (dt.name LIKE 'bar' OR dt.name LIKE '*bar' OR dt.name LIKE 'bar*' OR dt.name LIKE '*bar*')
)
// 11 more OR statements are here.
OR ( 1
AND (nd.manage_now_resource_id
LIKE 'foo' OR nd.manage_now_resource_id LIKE '*foo' OR nd.manage_now_resource_id LIKE 'foo*' OR nd.manage_now_resource_id LIKE '*foo*')
AND (nd.manage_now_resource_id
LIKE 'bar' OR nd.manage_now_resource_id LIKE '*bar' OR nd.manage_now_resource_id LIKE 'bar*' OR nd.manage_now_resource_id LIKE '*bar*')
) ORDER BY s.name, nd.device_name
That works.
However when I search for ANY words instead of ALL words, we get the following sql:
SELECT ... FROM ... WHERE 0
OR ( 0
OR (nd.device_name LIKE 'foo' OR nd.device_name LIKE '*foo' OR nd.device_name LIKE 'foo*' OR nd.device_name LIKE '*foo*')
OR (nd.device_name LIKE 'bar' OR nd.device_name LIKE '*bar' OR nd.device_name LIKE 'bar*' OR nd.device_name LIKE '*bar*')
)
OR ( 0
OR (dt.name LIKE 'foo' OR dt.name LIKE '*foo' OR dt.name LIKE 'foo*' OR dt.name LIKE '*foo*')
OR (dt.name LIKE 'bar' OR dt.name LIKE '*bar' OR dt.name LIKE 'bar*' OR dt.name LIKE '*bar*')
)
// 11 more OR statements are here.
OR ( 0
OR (nd.manage_now_resource_id
LIKE 'foo' OR nd.manage_now_resource_id LIKE '*foo' OR nd.manage_now_resource_id LIKE 'foo*' OR nd.manage_now_resource_id LIKE '*foo*')
OR (nd.manage_now_resource_id
LIKE 'bar' OR nd.manage_now_resource_id LIKE '*bar' OR nd.manage_now_resource_id LIKE 'bar*' OR nd.manage_now_resource_id LIKE '*bar*')
) ORDER BY s.name, nd.device_name
I get no results for the above sql statement, and when I copy and paste the full SQL into a query I get the "Query is too Complex" error.
As the sql is dynamically generated there's the possibilty for it to become even more complex (if, for example, the search string is 5 words, instead of only 2, or more searchable fields are later added).
If anybody has any idea on what I could do to simplify the sql here, so I don't get that error, it would be much appreciated.
If you want to see any more of my code, just ask.
