Messy Messy SQL

From: Jeff Wiegard <JWIEGARD_at_ngwmail.des.state.mn.us>
Date: Wed, 31 Oct 2001 08:56:08 -0800
Message-ID: <F001.003B968D.20011031091032@fatcity.com>

I've been asked to review some troublesome SQL queries, and since I don't know what to do with this one, I thought I'd see what you all can come up with.

They are doing the following:

select A.value 1, C.value2, A.value3
from Table A, Table B, Table C
where A.id = B.id
and B.id = C.id
and (A.ZIPS like ('%54016%')

or A.ZIPS like ( '%54021%') 
or A.ZIPS like ( '%54351%') 
or A.ZIPS like ( '%54025%') 
or A.ZIPS like ( '%54246%') 
or A.ZIPS like ( '%54071%') 
or A.ZIPS like ( '%54023%') 
or A.ZIPS like ( '%54029%') 
or A.ZIPS like ( '%54078%') 
or A.ZIPS like ( '%54651%') 
or A.ZIPS like ( '%54901%') 
or A.ZIPS like ( '%55514%'))

The only problem is that the actual data in the ZIPS field could be in the format of one of the following 3 samples:

55306:50

55358:100 56601:100

56301:25 56304:25 56379:25

The joins are on 400,000 row tables, and it's doing a full table scan on all three tables because of the first % wildcard in the like clause. It would be easy to get the values if they were all like the first format, because I could use 'substr(A.zips,1,5) = '55514'', but I don't know what to do with the second two samples, where there is more than one zipcode in the field.

I would like to be able to find the ':' character and subtract 5 to get the zipcode, but there could be 3 ':' characters corresponding to 3 zips in one field. Any ideas?

Thanks,

Jeff
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jeff Wiegard
  INET: JWIEGARD_at_ngwmail.des.state.mn.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Oct 31 2001 - 10:56:08 CST