Home » SQL & PL/SQL » SQL & PL/SQL » Is In operator will work?
icon1.gif  Is In operator will work? [message #193294] Fri, 15 September 2006 10:23 Go to next message
bineshc
Messages: 1
Registered: September 2006
Location: Bangalore
Junior Member

I have two tables in my apllication named A and B.
in table a I have a column named X_A and in table B i have a column named X_B.

In X_A I am storing a value. eg. abc

in X_B I am storing a value . Its a seqqunce of values separated by commas...
eg. abc,bca,hnb,hyn


My requirement is that , if the X_A value matches with any one of the value in the X_B column , I want to pass X_A value in to another column in another table.

Can anyone please tell me how this can be achieved?


my querry in the where clause was :

WHERE,
X_A in (''''|| REPLACE(X_B,';',''''||','||'''') ||'''') Mad

[Updated on: Fri, 15 September 2006 10:24]

Report message to a moderator

Re: Is In operator will work? [message #193355 is a reply to message #193294] Fri, 15 September 2006 16:55 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

You misunderstand the IN operator.

You should use the LIKE operator of maybe the INSTR operator.

Br
Kim
Re: Is In operator will work? [message #193513 is a reply to message #193294] Mon, 18 September 2006 01:28 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Hi,

Try using something of this sort. Assumption involved is T1 is having all the unique values and T2 will contain any combination of strings may or may not be having T1 values.

CREATE TABLE T1
(
B VARCHAR2(20)
)

CREATE TABLE T2
(
B VARCHAR2(20)
)


INSERT INTO T1 ( A, B ) VALUES (
1, 'abc');
INSERT INTO T1 ( A, B ) VALUES (
2, 'cde');
INSERT INTO T1 ( A, B ) VALUES (
2, 'gfji');


INSERT INTO T2 ( A, B ) VALUES (
2, 'uvwabcxyz');
INSERT INTO T2 ( A, B ) VALUES (
3, 'xyzabc');
INSERT INTO T2 ( A, B ) VALUES (
4, 'uvwabcxyz');

select X from (select t1.B X,count(CASE WHEN instr(t2.B,t1.B)>=1 THEN instr(t2.B,t1.B) ELSE NULL END) Y
from t1,t2 group by t1.B) where Y>0


Thanks
Re: Is In operator will work? [message #193522 is a reply to message #193294] Mon, 18 September 2006 02:04 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi again

How big will Your tables be?
How will You query the tables?

In Your current design, You will most likely need to full table scan both tables and then hash join.

If You changed the design to three tables instead, You could achieve much higher response time, if You would want to search specific records out only.

I have a function to transform the comma seperated list, but You would need to develop Your own code to maintain the tables.

Br
Kim
Previous Topic: Group statement
Next Topic: Calling Java services from PL/SQL
Goto Forum:
  


Current Time: Mon Dec 05 19:23:09 CST 2016

Total time taken to generate the page: 0.06804 seconds