Forum Stats

  • 3,815,793 Users
  • 2,259,086 Discussions
  • 7,893,236 Comments

Discussions

Getting data using IN operator

pradeep kateel
pradeep kateel Member Posts: 33
edited Mar 30, 2017 1:10AM in SQL & PL/SQL

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:

pastedImage_18.png

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

pastedImage_12.png

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,

Tagged:
John ThortonJohn StegemanFrank KulashChris Hunt
«1

Answers

  • mathguy
    mathguy Member Posts: 10,482 Blue Diamond
    edited Mar 29, 2017 8:38AM

    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 ThortonFrank Kulash
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 29, 2017 8:36AM

    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.

    Frank Kulash
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Mar 29, 2017 8:44AM

    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

    John ThortonChris HuntFrank Kulash
  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Mar 29, 2017 8:44AM

    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);

    Frank Kulash
  • pradeep kateel
    pradeep kateel Member Posts: 33
    edited Mar 29, 2017 8:52AM

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

    Regards,

  • pradeep kateel
    pradeep kateel Member Posts: 33
    edited Mar 29, 2017 8:56AM

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

    Regards,

  • Paulzip
    Paulzip Member Posts: 8,672 Blue Diamond
    edited Mar 29, 2017 8:56AM

    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')

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

    Easy huh?

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Mar 29, 2017 9:13AM

    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
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 29, 2017 9:15AM
    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.

    John Stegeman
  • pradeep kateel
    pradeep kateel Member Posts: 33
    edited Mar 29, 2017 9:42AM
    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.

This discussion has been closed.