Re: How to Create Local Temporary Table

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Fri, 21 Apr 2006 08:10:01 -0400
Message-ID: <4as0crFuprcqU1@individual.net>

Resant wrote:
> The query is part of procedure :
>
> SELECT xxx
> FROM (subquery) a,
> (
> SELECT xxx
> FROM hms_tpo_lp_plant a,(subquery) b
> WHERE [clause]
> ) b
> WHERE [clause]
>
> Where first subquery and second subquery are the the same.
> The subquery only access 1 table with where clause that use parameters.
> So what should I use to replace subquery??
Resant that is is a common table expression: WITH X AS (subquery)
  SELECT xxx
  FROM X a,

     (
     SELECT xxx
     FROM hms_tpo_lp_plant a, X b
     WHERE [clause]
     ) b

  WHERE [clause]

Both Oracle and SQL Server support this.

To answer your more general question, if I read the tea leafs correctly, in Oracle you would use VARRAY in conjunction with BULK COLLECT and FORALL where common table expression do not apply.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Received on Fri Apr 21 2006 - 07:10:01 CDT