Solved

Running into SQL stmt length limitation

Posted on 2003-11-23
17
794 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

789 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