Thread: IN Clause

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0

    IN Clause


    Hi All,

    I have a simple query like:

    select * From table1 where col1 in (1,2,3);

    Table1
    col1
    1 ...
    2 ...
    3 ...

    Table2
    1,2,3
    3,4

    I want to pick up the value "1,2,3" from another table. Is this possible? I know the following doesn't work:

    select * from table1 where col1 in (select value from table2)

    is there an alternative way?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0
    I did the following:

    select * From table1 where col1 in (WITH t AS
    (SELECT '1,2,3' AS str
    FROM DUAL)
    SELECT EXTRACTVALUE (VALUE (x), '/b') x
    FROM t,
    TABLE (XMLSEQUENCE (XMLTYPE ('<a><b>' || REPLACE (str, ',', '</b><b>') || '</b></a>').EXTRACT ('/*/*'))) x);
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    please don't do this. Storing multiple values in strings violates the principle of atomic values (see the first normal form) and forces you to come up with terrible workarounds. You basically have to circumvent the whole database system -- this also means no type safety, no referential integrity. Your strings can contain any garbage.

    Oracle DB is a relational database system, so store data in the relational way: as rows in tables.

    When you've fixed your Table2, you'll have no problems with the IN operator.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    16
    Rep Power
    0
    Yes, I completely understand that. But unfortunately, this is a legacy system and we are trying to leverage the current design. Hopefully, it will be resolved soon.

    Originally Posted by Jacques1
    Hi,

    please don't do this. Storing multiple values in strings violates the principle of atomic values (see the first normal form) and forces you to come up with terrible workarounds. You basically have to circumvent the whole database system -- this also means no type safety, no referential integrity. Your strings can contain any garbage.

    Oracle DB is a relational database system, so store data in the relational way: as rows in tables.

    When you've fixed your Table2, you'll have no problems with the IN operator.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Whoever designed the database of that legacy system should never be let near a computer again. Seriously.

    Check this workaround -- and let's hope your Oracle version isn't legacy as well. In any case, don't use this XML stuff. Bad design shouldn't be complemented with bad queries.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo