Re: maximum number of expressions in a list is 1000
- From: Hans Forbrich <fuzzy.graybeard@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 02 Jan 2013 10:22:57 -0700
On 02/01/2013 7:55 AM, Niall Litchfield wrote:
> I don't see why..
> [oracle@dev ~]$ sqlplus niall/niall
>
> SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 2 04:10:14 2013
>
> Copyright (c) 1982, 2010, Oracle. All rights reserved.
>
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
>
> SQL> create table t1(
> 2 id number
> 3 , content varchar2(50));
>
> Table created.
>
> SQL> insert into t1 (id,content) values (1, 'My Row');
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select content from t1 where id in (1);
>
> CONTENT
> --------------------------------------------------
> My Row
>
> SQL> c/(1/(1,1,1
> 1* select content from t1 where id in (1,1,1)
> SQL> /
>
> CONTENT
> --------------------------------------------------
> My Row
>
> SQL>
> SQL>
> SQL> create global temporary table gtt_id (id number);
>
> Table created.
>
> SQL> insert into gtt_id values (1);
>
> 1 row created.
>
> SQL> /
>
> 1 row created.
>
> SQL> /
>
> 1 row created.
>
> SQL> select content from t1 where id in (
> 2 select id from gtt_id);
>
> CONTENT
> --------------------------------------------------
> My Row
>
>
> obviously if the select also accessed the table to which we are joining
> then YMMV as they say.
(Chiming up, without having carefully studied entire thread ...)
In desperation, you could also use 'distinct'
[oracle@localhost ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 2 09:15:21 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL> create table t1 (
2 id number,
3 content varchar2(50));
Table created.
SQL> insert into t1 (id,content) values (1, 'My Row');
1 row created.
SQL> create global temporary table gtt_id (id number);
Table created.
SQL> insert into gtt_id values (1);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> rem Niall's original
SQL> select content from t1 where id in (
2 select id from gtt_id);
CONTENT
--------------------------------------------------
My Row
SQL> rem Adding 'distinct'
SQL> select content from t1 where id in (
2 select distinct(id) from gtt_id);
CONTENT
--------------------------------------------------
My Row
SQL> rem Join using distinct
SQL> select content from t1, (select distinct(id) from gtt_id) t2
2 where t1.id = t2.id;
CONTENT
--------------------------------------------------
My Row
SQL>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
