February 28th, 2013, 10:04 AM
I have a simple query like:
select * From table1 where col1 in (1,2,3);
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?
February 28th, 2013, 10:12 AM
I did the following:
select * From table1 where col1 in (WITH t AS
(SELECT '1,2,3' AS str
SELECT EXTRACTVALUE (VALUE (x), '/b') x
TABLE (XMLSEQUENCE (XMLTYPE ('<a><b>' || REPLACE (str, ',', '</b><b>') || '</b></a>').EXTRACT ('/*/*'))) x);
February 28th, 2013, 10:23 AM
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.
February 28th, 2013, 10:50 AM
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
February 28th, 2013, 11:51 AM
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.