By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

How to solve Complex Query Error !

P: n/a
Query is too complex
--------------------------------------------------------------------------------

Hi, I was trying to solve this problem since last two days but couldn't
find any solution.

I wanted to execute a query which is retrieving the records from table1
by
checking the condition for a long long string .
I'm using where clause and checking the condition as-

where (citycode=B and order_no=18900) or (citycode=B and
Order_no=18901).
.......so on upto (citycode=B and order_no=18950)
I'm assigning the string as follows

For i = 18900 To 18950
If i = 18900 Then
ord_string = Trim(ord_string) & "(ctCodOrd = 'B' and ord_no = " & i &
")"
Else
ord_string = Trim(ord_string) & " or (ctCodOrd = 'B' and ord_no = " & i
& ")"
End If
Next i

the query string is as follows-

sqlstr = "select * from track_det2 where " + Trim(ord_string)
Set dy = db.OpenRecordset(sqlstr, dbOpenDynaset)

After executing it is giving the error message " Query is too complex
!" .

I've also tried to solve it by giving the -

db.QueryTimeout = 100 (more than 60)
To give the database enough time to execute this query but it's not
worked.

Plz solve my problem.

amritesh

Oct 12 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"arun" <ar***********@gmail.comwrote in message
<11**********************@c28g2000cwb.googlegroups .com>:
Query is too complex
--------------------------------------------------------------------------------

Hi, I was trying to solve this problem since last two days but
couldn't find any solution.

I wanted to execute a query which is retrieving the records from
table1 by
checking the condition for a long long string .
I'm using where clause and checking the condition as-

where (citycode=B and order_no=18900) or (citycode=B and
Order_no=18901).
......so on upto (citycode=B and order_no=18950)
I'm assigning the string as follows

For i = 18900 To 18950
If i = 18900 Then
ord_string = Trim(ord_string) & "(ctCodOrd = 'B' and ord_no = " & i &
")"
Else
ord_string = Trim(ord_string) & " or (ctCodOrd = 'B' and ord_no = " &
i & ")"
End If
Next i

the query string is as follows-

sqlstr = "select * from track_det2 where " + Trim(ord_string)
Set dy = db.OpenRecordset(sqlstr, dbOpenDynaset)

After executing it is giving the error message " Query is too complex
!" .

I've also tried to solve it by giving the -

db.QueryTimeout = 100 (more than 60)
To give the database enough time to execute this query but it's not
worked.

Plz solve my problem.

amritesh
This seems a tad hardcoded, and gives a rather long SQL statement.

Another hardcoded version, could be

ord_string = "ctCodOrd = 'B' and ord_no Between 18900 and 18950"
sqlstr = "select * from track_det2 where " & ord_string

Or if a bit of dynamic is needed

For i = 18900 To 18950
' if some condition?
ord_string = ord_string & "," & cstr(i)
' end if
Next i
If Len(ord_string) Then
ord_string = "ctCodOrd = 'B' AND ord_no IN(" & _
mid$(ord_string, 2) & ")"
Else
ord_string = "ctCodOrd = 'B'"
End If

--
Roy-Vidar
Oct 12 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.