469,330 Members | 1,317 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

How to solve Complex Query Error !

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
1 2446
"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.

Similar topics

4 posts views Thread by Starbuck | last post: by
4 posts views Thread by ED | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.