Re: SQL Question

From: Tim Witort <trwNOSPAM_at_NOSPAMmedicalert.org>
Date: 1997/09/15
Message-ID: <341DBD25.3E21@NOSPAMmedicalert.org>#1/1

Lisa M. Lewis wrote:
>
> Hi all,
>
> I am trying to do something like:
>
> insert into temptable
> select userid
> from visits v, locations l
> where v.locationid = l.locationid
> and l.location like 'location1%'
>
> But instead of specifying a literal such as 'location1%', I would like
> to compare l.location to values held in a table. The key is I would
> still like to get the same results as a 'like' comparison would
> provide. This is what I did:
>
> insert into temptable
> select v.userid
> from visits v, location l, comparetable ct
> where v.location = l.location
> and SUBSTR(l.location,0,LENGTH( ct.location ) ) = ct.location;

The "0" in the substring should be "1".

> Can anyone tell me if there is a more efficient way of doing this either
> in SQL or PL/SQL?

Alternative:

 insert into temptable
 select v.userid
 from visits v, location l, comparetable ct  where v.location = l.location
 and l.location like ct.location

And load your comparetable with locations that you want to search for in the "like" format, i.e.

comparetable.location

---

location1%
location132%
this thing%
that thing
what%
%thingy

Since the like is acting as a join between your location table and the comparetable, you need to be careful that you don't place anything in the comparetable which would cause two hits for the same row on the location table:

Example:

location.location

---

location1 is the thingy
whatever is that thingy
location132

All these rows yeild two hits in the join to the comparetable, so they would generate two rows for your insert. The third one is deceptive - it yields a hit for location1% and location132%

• TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trwATmedicalertDOTorg || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

Received on Mon Sep 15 1997 - 00:00:00 CDT