RE: Using SQL to check for duplicate records

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

AFAIK, it is functionally the same as a view - the WITH ... AS (...) is 
executed on the fly, as is the SQL statement stored in a VIEW. The only 
difference is, this is not stored in an LF. A view has nothing but the 
statement to be executed - no index, nada, no access plan, etc.

Vern

At 12:02 PM 1/6/2004 -0500, you wrote:
> Not quite a view, but very similar, and lightning fast, was this:
> WITH
>   TEMPTABLE (IPROD) AS
>     (SELECT IPROD
>        FROM IIM
>        GROUP BY IPROD
>        HAVING COUNT(*)>1)
> SELECT TEMPTABLE.IPROD, RRN(A)
>   FROM TEMPTABLE, IIM A
>   WHERE TEMPTABLE.IPROD = A.IPROD
>
> Rob Berendt
>
> "Fisher, Don" <Dfisher@xxxxxxxxxxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 01/06/2004 11:34 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
> Subject
> RE: Using SQL to check for duplicate records
>
> Have you tried this?
> Create View IPRODDups as
>     SELECT IPROD FROM IIM group by IPROD
>     Having count(*) > 1
> Select IPROD,RRN(IIM) A join IPRODDups B
>     on A.IPROD = B.IPROD order by A.IPROD
>
> I've discovered that creating views can dramatically reduce the execution
> time of SQL statements in some circumstances.
>
> Donald R. Fisher, III
> Project Manager
> Roomstore Furniture Company
> (804) 784-7600 extension 2124
> DFisher@xxxxxxxxxxxxx