Home » SQL & PL/SQL » SQL & PL/SQL » "LIKE" query question (9i)
"LIKE" query question [message #405427] Wed, 27 May 2009 12:06 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
I know that this query doesn't work because it returns multiple values but I want to achieve something like this. Is it possible with a query or do I need a stored proc...

select * from ana_sample_details where uwi like (select uwi from ana_sample_points where locked = 1)

In other words I want this...

select * from ana_sample_details where uwi like ('%15W5%','%15W6%')

But I don't know the values at design time.

Re: "LIKE" query question [message #405428 is a reply to message #405427] Wed, 27 May 2009 12:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hmmmm.. You could try getting the values into a table, and then join ana_sample_details with like.

That would also work with Tom Kytes str2tbl for a generic input string.

Once you have the values in a table like this :

SQL> select * from table(str2tbl('15W5,15W6',','));

COLUMN_VALUE
--------------------------------------------------------------------------------
15W5
15W6

SQL>


You could then add a join like :

select * from (
  select * from table(str2tbl('15W5,15W6',','))
) tab1
join ana_sample_details
  on uwi like '%' || column_value || '%';

[Updated on: Wed, 27 May 2009 12:28]

Report message to a moderator

Re: "LIKE" query question [message #405429 is a reply to message #405427] Wed, 27 May 2009 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select DISTINCT a.* 
from ana_sample_details a, 
     (select DISTINCT uwi from ana_sample_points where locked = 1) v
where a.uwi like v.uwi
/

Regards
Michel

[Updated on: Wed, 27 May 2009 12:42]

Report message to a moderator

Re: "LIKE" query question [message #405431 is a reply to message #405429] Wed, 27 May 2009 12:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ha. I completely overlooked that he already had them in a table. Laughing

But if the uwi in ana_sample_points is only a substring of the uwi in ana_sample_details, then he would still have to add the % somewhere.
Re: "LIKE" query question [message #405432 is a reply to message #405431] Wed, 27 May 2009 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I only rewrote OP's query with a correct syntax and assumed that % or _ are in the data (as his query seems to show it), if not of course % should be added.

Regards
Michel
Re: "LIKE" query question [message #405441 is a reply to message #405427] Wed, 27 May 2009 15:32 Go to previous message
ferrethouse
Messages: 43
Registered: August 2006
Member
select DISTINCT a.* 
from ana_sample_details a, 
     (select DISTINCT uwi from ana_sample_points where locked = 1) v
where a.uwi like '%' || v.uwi || '%'



Brilliant! Thanks folks!

[Updated on: Wed, 27 May 2009 15:33]

Report message to a moderator

Previous Topic: last value/count before fetch from refcursor
Next Topic: Limitation in UTL_TCP Package
Goto Forum:
  


Current Time: Fri Dec 09 21:04:20 CST 2016

Total time taken to generate the page: 0.05036 seconds