Getting data using IN operator

pradeep kateel

Dear Friends,

I need to get the data from T2 using T1 column data. Basically T1 is holding code and description for the codes are in T2.

In table T1 data is stored as below:

Description for above code is in table T2 data stored as below:

Now I have written a code to retrieve the description using  T1 table data:

select upper('('''||replace(data1,',',''',''')||''')') from t1;

Result:

('2BA','2R','BAL','HI')

Using IN operator: When I run below code I am not able to get any data.

1. Is there any mistake in my code?

2. Is this the correct way to use the code like below ?

3. Do we have any other idea to get the correct data?

select data1_desc from t2 WHERE upper(data1_desc) IN (select upper('('''||replace(data1,',',''',''')||''')')  from t1);

Result : NULL

Codes to create the table and data:

create table t1 (data1 varchar2(200));create table t2 (data1 varchar2(200),data1_desc varchar2(2000));insert into  t1 values ('2BA,2R,BAL,HI');insert into t2 values ('2BA','2 Basket');insert into t2 values ('2R','2 Room');insert into t2 values ('BAL','Ball Room');insert into t2 values ('HI','High Floor with view');select * from t1;select * from t2;select upper('('''||replace(data1,',',''',''')||''')') from t1;select data1_desc from t2 WHERE upper(data1) IN (select ('('''||replace(data1,',',''',''')||''')')  from t1);

Regards,

mathguy

Are you saying that table t2 holds the descriptions for the codes in table t1, but table t2 does NOT have the codes, it only has the descriptions? And you must guess which description goes with which code?

That makes no sense. Is this a production system, or some "school project" of some kind? What are you supposed to do with the code '2BA', if in t2 you have descriptions '2 basket' and '2 balloon'?  Which one is '2BA'? If you answer, how did you determine that answer?

John Thorton

First Normal Form dictates that only single value is stored in single column.

the data "model" is flawed & needs to be corrected before proceeding.

Rows in a table are like balls in a basket. There is NO inherent row order in Oracle Heap table.

John Stegeman

There's so much wrong here. Storing multiple values in a CSV column. Having no relation of codes to descriptions. Building a dynamic string of CSV values and expecting it to work in an IN clause. None of those things will work

Saubhik

Normalize your design.

with nrml_t1 as

  (select regexp_substr(data1, '[^,]+', 1, level, 'i') data1

  from t1

    connect by level   <= regexp_count(data1, ',') + 1

  and prior sys_guid() is not null

  )

select nrml_t1.data1,

  t2.data1_desc

from nrml_t1,

  t2

where upper(substr(replace(t2.data1_desc,' '),1,length(nrml_t1.data1))) = upper(nrml_t1.data1);

pradeep kateel

My apologies. I have missed to create one column before posting. Now my question is modified. Please check.

Regards,

pradeep kateel

My apologies. I have missed to create one column before posting. Now my question is modified. Please check.

Regards,

Paulzip

As others have said, your data model is completely wrong.

Try doing it properly, something like this...

create table t1 (

  id          varchar2(5),

  description varchar2(100),

  constraint pk_t1 primary key (id)

);

insert into t1 (id, description) values ('2BA', '2 Basket');

insert into t1 (id, description) values ('BAL', 'Ball Room');

insert into t1 (id, description) values ('2R',  '2 Room');

insert into t1 (id, description) values ('HI',  'High Floor with view');

commit;

select *

from t1

where id in ('2BA', '2R', 'BAL', 'HI')

ID	DESCRIPTION
'2BA'	'2 Basket'
'2R'	'2 Room'
'BAL'	'Ball Room'
'HI'	'High Floor with view'

Easy huh?

Saubhik

You should be able to solve this by the input already given to you, anyway you should change your design. This is kind of Varying IN List. The Tom Kyte Blog: Varying in lists... 

with nrml_t1 as

  (select regexp_substr(data1, '[^,]+', 1, level, 'i') data1

  from t1

    connect by level   <= regexp_count(data1, ',') + 1

  and prior sys_guid() is not null

  )

select nrml_t1.data1,

  t2.data1_desc

from nrml_t1,

  t2

where nrml_t1.data1=t2.data1;

EdStevens

pradeep kateel wrote:My apologies. I have missed to create one column before posting. Now my question is modified. Please check.Regards,

Great.  Now we have discussion/responses that refer to code/text that is no longer in the thread.  Just adding to the confusion.

When you want to correct something you've posted, you should put it in a new message so that the subject of previous messages remains intact, preserving the integrity of the entire thread.

Also, it is not necessary to post the same exact reply to multiple people.

pradeep kateel

where id in ('2BA', '2R', 'BAL', 'HI')

in here need to get the data from column data1 from table t1. Because data in the data1 of table t1 is not constant.