We have a query with a huge lists of ID's in an IN list......such as SELECT * WHERE id IN(id1, id2, id3.....id1400) list.

Is there a way  to change it so that the  the big "IN" lists could be changed so that the "IN" list was populated into a temp table first and then joined to to the rest of the query.

i.e.  In the query below, it would change to "select * from a into temp mytemp"

with a(mycol) as (values('a'),('b'),('c')) 
select * from a

 MYCOL
 -----
 a
 b
 c



thanks in advance.

On Tuesday, 30 September 2014 21:17:00 UTC+1, tom...@gmail.com  wrote:
> We have a query with a huge lists of ID's in an IN list......such as SELECT * WHERE id IN(id1, id2, id3.....id1400) list.
> 
> 
> 
> Is there a way  to change it so that the  the big "IN" lists could be changed so that the "IN" list was populated into a temp table first and then joined to to the rest of the query.
> 
> 
> 
> i.e.  In the query below, it would change to "select * from a into temp mytemp"
> 
> 
> 
> with a(mycol) as (values('a'),('b'),('c')) 
> 
> select * from a
> 
> 
> 
>  MYCOL
> 
>  -----
> 
>  a
> 
>  b
> 
>  c
> 
> 
> 
> 
> 
> 
> 
> thanks in advance.

Use an external file?

create temp table tmp_ids (id integer) with no log;

load from "id_file" insert into tmp_ids;

select * from TABLE where id in (select id from tmp_ids);