Home » SQL & PL/SQL » SQL & PL/SQL » Join question (Oracle 10gR2 RHEL4)
Join question [message #321600] Tue, 20 May 2008 16:53 Go to next message
shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI
Senior Member
I think I am a little rusty here. Am I missing something? When I run this query I get no rows returned. And I know that there are rows for sure, as I double checked.

select c.owner, c.table_name, c.column_name, c.data_type, t.tablespace_name
from dba_tab_columns c, dba_tables t
where c.data_type IN ('LOB','CLOB','BLOB')
and t.tablespace_name IN ('%DAT%', '%IDX%')
and c.table_name = t.table_name
and c.owner = t.owner;

Thank you.
Re: Join question [message #321602 is a reply to message #321600] Tue, 20 May 2008 17:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot combine wildcards with IN
Re: Join question [message #321627 is a reply to message #321600] Tue, 20 May 2008 21:32 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Shaseeb, as Frank points out the % wild card is invalid.

Two quick options -
You could replace 'IN' with 'LIKE' and make an OR condition:

Where tspace like '%val1%' or tspace like '%val2%'.

Other option would be the INSTR() "in string" function -

Where instr(tspace,val1) > 0 or instr(tspace,val2)> 0.

Regards,
Harry
Re: Join question [message #321850 is a reply to message #321627] Wed, 21 May 2008 10:03 Go to previous message
shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI
Senior Member
Ahah! Thank you Frank and Harry.

shaseeb
Previous Topic: triggers
Next Topic: Move LOB to new tablespace but still visible in old...
Goto Forum:
  


Current Time: Sun Dec 11 02:41:14 CST 2016

Total time taken to generate the page: 0.29202 seconds