Re: maximum number of expressions in a list is 1000

From: Hans Forbrich <fuzzy.graybeard_at_gmail.com>
Date: Wed, 02 Jan 2013 10:22:57 -0700
Message-ID: <50E46CF1.1080103_at_gmail.com>

On 02/01/2013 7:55 AM, Niall Litchfield wrote:
> I don't see why..
> [oracle_at_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_at_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>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 02 2013 - 18:22:57 CET