Re: Messy Messy SQL

Jeff,

That's a pretty silly way to store zip codes. Could you create a zipcode-id table, where you break out each zip and its id? Then search that table and join back to table a?

Or, if you're at 8i, and if you only get up to a really small number (like the 3 you display) of zips in a combined zip field, you could create the first index on the zip field, and two additional functionbased  indexes on the second and third zip codes. Then instead of using like '%99999%', you could use the function with which you created the index, like where substr(instr(zip_field, ' ')+1) like '99999%'.

Some thoughts, hope they help.

God bless America.

Yosi

Jeff Wiegard wrote:

> 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: Yosi Greenfield
  INET: yosi_at_comhill.com

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