Running into SQL stmt length limitation

Hi,
I have a very long SQL stmt and cannot be shortened. And I seem to be running into some sort of limitation on the length of the stmt. I'm search based on street names, i.e. zip = 12345 and (stn like 'main%' or stn like 'abc%'.....). The syntax is correct and when i add one more street, it gives me "syntax error".
Is there a way to go around this limitation?
Thank you,
Phil
spoowizAsked:
Who is Participating?
 
jobrienctConnect With a Mentor Commented:
I believe your problem is with the OR statements rather then the SQL length limitations. Search help for Microsoft access specifications and sleect the query topic. It talks about AND but I would assume this applies to OR as well.

Microsoft Access database query specifications
Attribute Maximum
Number of enforced relationships 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query 32
Number of fields in a recordset 255
Recordset size 1 gigabyte
Sort limit 255 characters in one or more fields
Number of levels of nested queries 50
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255

Number of ANDs in a WHERE or HAVING clause 40

Number of characters in an SQL statement approximately 64,000

So you will simply have to recude the OR statements per query or use another dbms I think.

regards,

John
0
 
sirbountyCommented:
I can't imagine your statement exceeding any limitation I've ever witnessed.
Can you post your string here?
0
 
spoowizAuthor Commented:
Below are 2 stmts. Now, I have it split up because of the "limitation". I am using MSA2000.

ZIP=94539  and (
stn like 'canyon Heights%' or
stn like 'kimber %' or
stn like 'kimberwood%' or
stn like 'estrella %' or
stn like 'benavente%' or
stn like 'platero%' or
stn like 'fisalia%' or
stn like 'hidalgo%' or
stn like 'orden%' or
stn like 'obispo%' or
stn like 'almeria%' or
stn like 'e las palmas%' or
stn like 'dolerita%' or
stn like 'valais%' or
stn like 'celada%' or
stn like 'delegado%' or
stn like 'madrid%' or
stn like 'imperio%' or
stn like 'loro %' or
stn like 'tordo%' or
stn like 'segovia%' or
stn like 'melendez%' or
stn like 'britto%' or
stn like 'mackintosh%' or
stn like 'slayton%' or
stn like 'palatino%' or
stn like 'recino%' or
stn like 'esparito%' or
stn like 'gaucho%' or
stn like 'abuelo%' or
stn like 'clara %' or
stn like 'lucy %' or
stn like 'palacio%' or
stn like 'castro %' or

stn like 'las palmas%' or
stn like 'san moreno%' or
stn like 'san simeon%' or
stn like 'catalina%' or
stn like 'lucinda%' or
stn like 'san sebastian%' or
stn like 'san carlos%' or
stn like 'hacienda%' or
stn like 'seville%' or
stn like 'andorra%' or
stn like 'valencia%' or
stn like 'la jolla%' or
stn like 'carmelita%' or
stn like 'el dorado%' or
stn like 'dalgo%' or
stn like 'caliente%' or
stn like 'helado%' or
stn like 'encanto%' or
stn like 'ondina%' or
stn like 'jacaranda%' or
stn like 'ferreira%' or
stn like 'amapola%' or
stn like 'mission villa%' or
stn like 'los pinos%' or
stn like 'optimo%' or
stn like 'tirso%' or
stn like 'rainwater%' or
stn like 'lemos%' or
stn like 'chiltern%' or
stn like 'laguna%' or
stn like 'ambar %' or
stn like 'gualala%' or
stn like 'capa %' or
stn like 'marino%' or
stn like 'laredo%' or
stn like 'casa marcia%' or
stn like 'quintana%' or
stn like 'la purissima%' or
stn like 'valdez%' or
stn like 'dolores%' or
stn like 'bermuda%' or
stn like 'bjada%' or
stn like 'debora%' or
stn like 'camero %' or
stn like 'canto%' or
stn like 'durillo%' or
stn like 'cascado%' or
stn like 'cantere%' or
stn like 'gomes%' or
stn like 'pajaro%' or
stn like 'marabu%' or
stn like 'calido%' or
stn like 'mino %' or
stn like 'una %' or
stn like 'vaca %' or
stn like 'oro %')

---------
GOMES ELEMENTARY - BY STREET
Syntax too long if combined above
south of Paseo Padre

ZIP=94539  and (
stn like 'driscoll%' or
stn like 'shadow brooke%' or
stn like 'terry%' or
stn like 'marty%' or
stn like 'nunes%' or
stn like 'cruz%' or
stn like 'rioja%' or
stn like 'valero%' or
stn like 'sueno%' or
stn like 'abaca%' or
stn like 'corriea%' or
stn like 'bairo%' or
stn like 'davila%' or
stn like 'joyce%' or
stn like 'kalenda%' or
stn like 'cornac%' or
stn like 'ramon %' or
stn like 'briscoe%')
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ornicarCommented:
Simply create a table (SearchSTN) with one column and fill it with all these values. You will then have a statement like:

Select * from Clients,SearchSTN where zip = 94539 Clients.stn like SearchSTN.stn & "%"
0
 
spoowizAuthor Commented:
ornicar,
that sure is a workaround. however, not a good one. i do many different searches like this. this workaround is not suitable.
thanks,
phil
0
 
peter57rCommented:
Try this :

select  stn as A, stn as B from mytable where
A= ........or
A=........or
A=......


or
B=.....or
B=.....or
B=....or
....

Pete
0
 
meciabConnect With a Mentor Commented:
You just have to do a function who make successive query
where criteria is a table whit your criterias

function returnResRS(Criteria(), NumCriterias as integer) as recordset
dim i as integer
for i=0 to NumCriterias
   
strSQL="SELECT details.*, details.tableau FROM details  WHERE " & criteria(i) & ";"

next
0
 
meciabCommented:
Sorry I havn't finish my answer...

You just have to do a function who make successive query
where criteria is a table whit your criterias

function returnResRS(Criteria(), NumCriterias as integer) as recordset
dim i as integer
dim MaxCriteriasPerQuery as integer
dim strSQL1 as string
dim strSQL2 as string

NumCriterias=0
strSQL1="SELECT *, Address FROM details  WHERE "

for i=0 to NumCriterias
    strSQL2=strSQL2 & criteria(i)
    NumCriterias=NumCriterias+1
    if NumCriterias=MaxCriteriasPerQuery then
       dbs.CreateQueryDef "Query" & i, strSQL1 & strSQL2
       strSQL1="SELECT *, Address FROM Query" & i & "WHERE "     
    end if
next

this function is not finalized but that's the way...
0
 
stevbeConnect With a Mentor Commented:
if your street names are in another table you can build a subselect statement ...

SELECT tblClient.*
FROM tblClient
WHERE (((tblClient.Streetname)=(SELECT tblStreet.StreetName FROM tblStreet WHERE (((tblStreet.StreetName) Like [tblClient].[Streetname] & "*")))));

Steve
0
 
stevbeCommented:
sorry, forgot to add your zipcode criteria ...

SELECT tblClient.*
FROM tblClient
WHERE (((tblClient.Streetname)=(SELECT tblStreet.StreetName FROM tblStreet WHERE (((tblStreet.StreetName) Like [tblClient].[Streetname] & "*"))))) AND ((tblClient.Zipcode)=90210));

Steve
0
 
ornicarCommented:
Stevbe, if you'd read previous comments, you'd realize that this solution isn't accepted by Spoowiz. However, I think this is the best and easiest solution.
0
 
spoowizAuthor Commented:
jobrienct is probably right about the max number of ORs and ANDs.
There is no way to increase that?
I wonder if a stored procedure would have the same limitation.
0
 
meciabCommented:
Same comments as ornicar...
0
 
ornicarConnect With a Mentor Commented:
A stored procedure. Is it a SQL-Server database? Worth to be tried if it is the case.

Maybe you can try several queries, then a UNION SELECT statement?

Define StreetQuery1 with:


Comment from sirbounty
Date: 11/24/2003 05:53AM CET
Comment

I can't imagine your statement exceeding any limitation I've ever witnessed.
Can you post your string here?

Comment from spoowiz
Date: 11/24/2003 05:58AM CET
Author Comment

Below are 2 stmts. Now, I have it split up because of the "limitation". I am using MSA2000.

ZIP=94539  and (
stn like 'canyon Heights%' or
stn like 'kimber %' or
...

then StreetQuery2 with:
ZIP=94539  and (
stn like 'driscoll%' or
stn like 'shadow brooke%' or
...

maybe more of them, finally unioning all these queries:

select * from StreetQuery1
union
select * from StreetQuery2

Would not updateable but maybe you don't need.
0
 
spoowizAuthor Commented:
Thank you all.
jobrienct identified the real problem so he got the most points.
Other solutions were good but unfortunately, in my case, it's not workable but it does give me some new ideas.
thanks again.
0
 
jobrienctCommented:
Glad we could help. I'm only sorry it doesnt resolve your issues. Frankly I think ornicar's original suggestion is on the money even if it requires redesigning the tables. There is no end to the number of streets that could be added and as such that data needs to reside in a table rather then as constants in a query IMO.

thanks for the points and good luck :)
0
 
spoowizAuthor Commented:
that solution requires me to have more tables (or flags within tables) for different searches. the search string above is only one of many i do. that's why that solution is not ideal. i may be able to use that idea and section off different portions of the city and code them into the table. i may do it if i Have to. thanks again.
0
All Courses

From novice to tech pro — start learning today.