Select all rows which occure in list of 2927 id's

Question

I have a list of 2927 id's. I want to get all rows which id's are in that list. How can I achieve this? It is a comma seperated list of id's. An in statement won't work since there is a limit of 1000. I've tried solutions like this Loop through pre defined values but it doesn't do what I expect.

I'm using toad and I wish to see the rows in the datagrid (multiple rows, multiple columns).

Thanks in advance!

How a list of id's may look like:

67,122,173,256,284,285,288,289,291,294,296,298,301,320,346,359,366,425,428,454,528,573,576,584,593,654,654,694,722,838,1833,1976,1979,1979,2002,2004,2005,2045,2083,2109,2114,2126,2126,2157,2204,2204,2211,2212,2332,2576,...

How the statement would be when the limit of 1000 isn't reached:

Select * from tablename where tablename.id in (67,122,173,256,284,285,288,289,291,294,296,298,301,320,346,359,366,425,428,454,528,573,576,584,593,654,654,694,722,838,1833,1976,1979,1979,2002,2004,2005,2045,2083,2109,2114,2126,2126,2157,2204,2204,2211,2212,2332,2576);

this will do the trick: https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement — neurotic-d, Jun 30 '16 at 08:56
Why are you using IDs like that ? If they're above 1000, you should consider putting them in a table. — Pirate X, Jun 30 '16 at 09:08
Like jarlh said, put them in a temp table. A temp table is not a database alteration. — HoneyBadger, Jun 30 '16 at 09:15
@TomB. The list of Ids, where you getting them from ? If you're obtaining using a sql query. Simply put that query as subquery. `Select * from tablename where tablename.id in (Select id from yourtablename)` — Pirate X, Jun 30 '16 at 09:16
Since I don't have the knowledge of temp tables, some help would be appreciated — Tom B., Jun 30 '16 at 09:16
@PirateX the list is externaly deliverd from within our company (plain text file) so no sql query. — Tom B., Jun 30 '16 at 09:19
@TomB. [See this answer](http://stackoverflow.com/a/14643253/4046274). I am not sure if you have privileges to create temp table in PROD. Just try though. — Pirate X, Jun 30 '16 at 09:21

score 1 · Answer 1 · answered Jun 30 '16 at 13:58

Here's another way to approach it by turning the IDs into a logical table using a Common Table Expression (CTE) then joining like usual. Might be easier to get your head around it thinking of it this way:

-- Build the list of IDs.
with data(str) as (
    select '67,122,173,256,284,285,288,289,291,294,296,298,301,320,346,359
     ,366,425,428,454,528,573,576,584,593,654,654,694,722,838,1833,1976,1979,1979,2002
     ,2004,2005,2045,2083,2109,2114,2126,2126,2157,2204,2204,2211,2212,2332,2576' 
    from dual
),
-- Turn the list into a table using the comma as the delimiter. Think of it
-- like a temp table in memory.
id_list(id) as (
  select regexp_substr(str, '(.*?)(,|$)', 1, level, NULL, 1)
  from data
  connect by level <= regexp_count(str, ',') + 1
)
-- Select data from the main table, joining to the id_list "temp" table where
-- the ID matches.
select tablename.*
from tablename, id_list
where tablename.id = id_list.id;

score 0 · Answer 2 · answered Jun 30 '16 at 09:32

0

Slow, ugly, but working solution: split the string to ids and use cursor to get the rows OR
generate a dynamic select query,based on the string: 'select * from tbl where id = 1 or id = 2 or ... ' and execute it, wont be fast again but will work.

answered Jun 30 '16 at 09:32

Dexion

1,101
8
14

score 0 · Answer 3 · answered Jun 30 '16 at 10:19

Not to be used in production:

with list as (
    select to_number(regexp_substr('67,122,173,256,284,285,288,289,291,294,296,298,301,320,346,359
     ,366,425,428,454,528,573,576,584,593,654,654,694,722,838,1833,1976,1979,1979,2002
     ,2004,2005,2045,2083,2109,2114,2126,2126,2157,2204,2204,2211,2212,2332,2576'
     , '\d+', 1, level)) id from dual connect by level < 5000
)
select * from tablename where tablename.id in (select id from list where id is not null);

score 0 · Answer 4 · 2016-06-30T12:31:53.687

You can use something like this:

... 
where ',' || id_list || ',' like '%,' || to_char(id) || ',%'

You do need to add commas around to_char(id) (otherwise 3854 will match 38), and you need to add commas around the id_list so the first and last value will match (the commas around id_list are only needed because you need the commas around to_char(id)).

This assumes id has data type NUMBER. If instead it is already VARCHAR2 or some other character data type, you just need id instead of to_char(id). (Strictly speaking, to_char() is not needed even when id is NUMBER, but as a best practice one should not rely on implicit conversions.)

Example:

with
     mytable(id, val) as (
       select 100, 'abc' from dual union all
       select 173, 'z'   from dual union all
       select 250, 'dvd' from dual union all
       select  30, 'vv'  from dual union all
       select 359, 'ghi' from dual
     ),
     test_data (id_list) as (
       select '67,122,173,256,284,285,288,289,291,294,296,298,301,320,346,359'
         from dual
     )
select mytable.* from mytable, test_data
where ',' || id_list || ',' like '%,' || to_char(id) || ',%'
;

        ID VAL
---------- ---
       173 z
       359 ghi

Select all rows which occure in list of 2927 id's

4 Answers4

Related