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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
jobrienctCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
meciabCommented:
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
stevbeCommented:
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
ornicarCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.