Let's take this query:
WITH temp as SELECT '123455' colum from dual
SELECT * FROM big_table WHERE cod IN (SELECT colum from temp)
UNION ALL
SELECT * FROM big_table2 WHERE cod IN (SELECT colum from temp)
I'd like to search for a list of values as well as I can look for one single, but how can I build a list of rows without having to write a lot of UNION?
If you have a string table type available, then the following script might do what you want
create table big_table
(
cod varchar2(4000)
);
create table big_table2
(
cod varchar2(4000)
);
insert into big_table (cod) values ('12345');
insert into big_table (cod) values ('12346');
insert into big_table (cod) values ('12347');
insert into big_table (cod) values ('12345');
insert into big_table (cod) values ('12348');
insert into big_table (cod) values ('12349');
--Example usage of the custom defined type stringarray
SELECT column_value from table(stringarray('12345','12348'));
WITH temp as (SELECT column_value from table(stringarray('12345','12348')))
SELECT * FROM big_table WHERE cod IN (SELECT column_value from temp)
UNION ALL
SELECT * FROM big_table2 WHERE cod IN (SELECT column_value from temp);
drop table big_table;
drop table big_table2;
You can create the stringarray type like this
CREATE OR REPLACE TYPE STRINGARRAY as table of varchar2(30)
I hope that answers your question.
I'm not sure if Oracle supports it (or if this is exactly what you're asking), but if it's like DB2, you can use your Common Table Expression to build out your value list like this...
WITH temp (colum) as (VALUES '123456', '789012', '345678')
SELECT * FROM big_table WHERE cod IN (SELECT colum from temp)
UNION ALL
SELECT * FROM big_table2 WHERE cod IN (SELECT colum from temp)
As for not having to write a load of UNIONS, I don't think that there's a better way to do what you're asking for...
If you're searching multiple tables, then you can't avoid using UNION if you want to do it in a single set operation.
Otherwise, you can pass an array of table names to a loop, and just dynamically write and then run the sql. I'd rather do unions, personally...
temp?