I received the following error last week:

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

Here is a good article describing the problem: http://rapidapplicationdevelopment.blogspot.com/2007/05/caml-nested-too-deep.html

The article describes the cause of the error was trying to insert a large amount of list items in a batch query. The solution is to break up the batch into smaller chunks. However I was doing a select query. After some googling and coming up short, I copied the CAML query into a text file and took a look at it. The query was several hundred lines long. It seems that the where clause of the CAML query was being added dynamically inside a loop like this

foreach (HistoryItem distinctItem in distinctItems)
{
             spQuery.Or(HistoryListColumns.Number, SPFieldTypes.Text.ToString(), distinctItem.Number);
}

This loop builds up a CAML query by adding an OR element for each item in the distinctItems collection. This worked fine until distinctItems grew to over 1,000 items making the resulting CAML query too long for SharePoint/SQL to parse it. The simple solution for me was to break distinctItems up into subLists of 200 items and create separate CAML queries for each then combine the results.

So remember if you need to dynamically build your CAML queries pay attention to the size and how big the query could possibly get.