Re: SQL Question

What's about something like that

declare
CURSOR ct = select location from comparetable; begin
for ct in cc do loop
insert into temptable
select userid
from visits v, locations l
where v.locationid = l.locationid
and l.location like cc.location||'%'
end loop;
END; 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;
>
> Can anyone tell me if there is a more efficient way of doing this either
> in SQL or PL/SQL?
>
> Thanks for your time!
>
> Lisa
>
> P.S. Please respond by e-mail.
Received on Tue Sep 16 1997 - 00:00:00 CDT