Solved

Running into SQL stmt length limitation

Posted on 2003-11-23
17
763 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:spoowiz
  • 5
  • 3
  • 3
  • +4
17 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 9808372
I can't imagine your statement exceeding any limitation I've ever witnessed.
Can you post your string here?
0
 

Author Comment

by:spoowiz
ID: 9808385
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
 
LVL 9

Expert Comment

by:ornicar
ID: 9808471
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
 

Author Comment

by:spoowiz
ID: 9808494
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
 
LVL 10

Accepted Solution

by:
jobrienct earned 150 total points
ID: 9809309
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
 
LVL 77

Expert Comment

by:peter57r
ID: 9809435
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
 

Assisted Solution

by:meciab
meciab earned 50 total points
ID: 9809702
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
 

Expert Comment

by:meciab
ID: 9809739
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 25 total points
ID: 9812749
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
 
LVL 39

Expert Comment

by:stevbe
ID: 9812763
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
 
LVL 9

Expert Comment

by:ornicar
ID: 9812802
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
 

Author Comment

by:spoowiz
ID: 9812851
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
 

Expert Comment

by:meciab
ID: 9816436
Same comments as ornicar...
0
 
LVL 9

Assisted Solution

by:ornicar
ornicar earned 25 total points
ID: 9817160
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
 

Author Comment

by:spoowiz
ID: 9818757
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
 
LVL 10

Expert Comment

by:jobrienct
ID: 9819033
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
 

Author Comment

by:spoowiz
ID: 9819080
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now