Forum Stats

  • 3,815,817 Users
  • 2,259,093 Discussions
  • 7,893,255 Comments

Discussions

Getting data using IN operator

2»

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 29, 2017 9:52AM
    pradeep kateel wrote: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. 

    The whole "design" is flawed & should be thrown away.

    Don't store multiple values in single column.

    Don't store SQL clauses in table column.

    Having a one column table indicates designer needs to be (re)trained.

    Frank Kulash
  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy
    edited Mar 29, 2017 9:58AM
    pradeep kateel wrote: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. 

    As you're being told, don't do that. Storing multiple values in a column goes against good design principles for a database.

    If t1 is the table as described by you, then the data1 table just needs to be 1 or more rows containg the values you want.

    create table data1 (

    col varchar2(20)

    );

    insert into data1(col) values('2BA');

    insert into data1(col) values('2R');

    etc.

    Then

    select * from t1 where t1.data1 in (select col from data1);

    The 'dynamic' bit is now that you have more or less rows in the table data1

    rather than one or more values in a single column.

    And see how easy it is now!

    Frank Kulash
  • Paulzip
    Paulzip Member Posts: 8,672 Blue Diamond
    edited Mar 29, 2017 10:10AM
    pradeep kateel wrote: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. 

    You've changed information since I posted.

    Don't store multiple values in a column, it is a hack!!!!  It breaks good DB design paradigms.  If you want to store multiple values, you have can do something like this.. (following on from my previous post)

    create table combos (

      combo_id integer,

      id integer,

      constraint pk_combos primary key (combo_id, id),

      constraint fk_t1 foreign key (id) references t1(id) on delete cascade

    );

    insert into combos(combo_id, id) values (1, '2BA');

    insert into combos(combo_id, id) values (1, '2R');

    insert into combos(combo_id, id) values (2, '2BA');

    insert into combos(combo_id, id) values (2, '2R');

    insert into combos(combo_id, id) values (2, 'BAL');

    insert into combos(combo_id, id) values (2, 'HI');

    commit;

    select *

    from t1

    where id in (select id from combos where combo_id = 2)

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

    select *

    from t1

    where id in (select id from combos where combo_id = 1)

    IDDESCRIPTION
    '2BA''2 Basket'
    '2R''2 Room'
    Frank Kulash
  • CarlosDLG
    CarlosDLG Member Posts: 1,378 Gold Trophy
    edited Mar 29, 2017 11:16AM

    You have been told several times that your design is flawed, and it must be changed if you want to do things correctly and save yourself from future problems, but it appears that you are ignoring those recommendations.

    If you definitely can't change the design, then say so, but don't ignore what people is telling you.

    It would be very wise to try to convince whoever has the authority to change the design, instead of trying to solve this particular problem, because your life will be much more complicated in the future if you continue with that flawed design.

    However, if changing the design is not possible, please read this:

    Frank Kulash
  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited Mar 29, 2017 11:40AM

    Like the other guys have said, your design is broken. It'll be much less work to fix it than to work around it.

    Also, it's not obvious from your question what output you are trying to achieve.

  • JPDominator
    JPDominator Member Posts: 60
    edited Mar 29, 2017 1:37PM

    There's a use-case for everything. This does exactly what you want it to, but obviously has limitations.

    create or replace TYPE data_arrayAS VARRAY(32767) OF VARCHAR2(4000);/CREATE OR REPLACE FUNCTION SPLIT (the_input varchar2, the_delimeter varchar2 default ',')return data_arrayis  parsed_data    data_array;  found_value varchar2(4000 BYTE);  v_parse_me varchar2(4000 BYTE);  v_instr number;BEGIN  v_parse_me := the_input;  parsed_data := data_array();  while v_parse_me is not null    loop      parsed_data.extend;      v_instr := instr(v_parse_me,the_delimeter);      if v_instr = 0        then          found_value := trim(v_parse_me);          v_parse_me  := null;      elsif v_instr > 0        then          found_value := trim(substr(v_parse_me,1,v_instr-1));          v_parse_me  := trim(substr(v_parse_me,v_instr+1));        else found_value := null;      end if;      parsed_data(parsed_data.last) := found_value;    end loop;return parsed_data;END SPLIT;/

    select *  from t2where data1 in (select *                  from table(select split(data1)                                from t1));

    DATA1DATA1_DESC
    2BA2 Basket
    2R2 Room
    BALBall Room
    HIHigh Floor with view
  • Peter vd Zwan
    Peter vd Zwan Member Posts: 724 Gold Badge
    edited Mar 29, 2017 2:05PM

    Hi,

    As others said already, this is not the best way to store your data.

    But I understand you want to list all rows of T2 for which T2.data1 is contained in T1.data1

    You tried to make a string and give that to the in operator that is posible but remember that is stil only one string: like ('2BA','2R','BAL','HI') 

    This is not a list of 4 strings in parentheses.

    To check if T2.data1 is part of the one string T1.data1 you can use the instr function like this:

    with t1 as(select '2BA,2R,BAL,HI' data1 from dual), t2 as(select '2BA' data1, '2 Basket'              data1_desc from dual union allselect '2R'  data1, '2 Room'                data1_desc from dual union allselect '3R'  data1, '3 Room'                data1_desc from dual union all    --added for testing. this should not show in resultsselect 'BAL' data1, 'Ball Room'             data1_desc from dual union allselect 'HI'  data1, 'High Floor with view'  data1_desc from dual)select  *from  t2where  instr(      (select ',' || data1|| ',' from t1)      , ',' || t2.data1 || ',' ) > 0;DATA1 DATA1_DESC         
    ----- --------------------
    2BA   2 Basket            
    2R    2 Room              
    BAL   Ball Room           
    HI    High Floor with view

    Regards,

    Peter

  • venkatesh867
    venkatesh867 Member Posts: 82
    edited Mar 30, 2017 1:10AM

    select data1_desc

      from t2

    WHERE upper(data1) IN

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

              from t1

            connect by regexp_substr(data1, '[^,]+', 1, level) is not null);

This discussion has been closed.