Please REMOVE ME from list:)
-----Original Message-----
From: Jay Walters <jwalters@computer.org>
To: informix-list@iiug.org <informix-list@iiug.org>
Date: Sunday, March 28, 1999 4:42 PM
Subject: Temp table question
>How expensive is it to use an explicit temp table (select ... into temp
>...) followed by drop table ..., vs an implicitly created temp table
>created within a select for use by an order by clause?
>
>How does this compare to creating a view (which will require the use of
>an implicit temp table)?
>
>I will only use the object once and will then drop it.
>
>In my sql I essentially need to count both the number of unique items
>(multiple rows in the result set per item) and the average number of
>rows/item in the result set - something like
>
>select count(f0.id)/count(distinct f0.id),
> count(distinct f0.id)
> from foo f0
> where f0.val = 'A' or f0.val = 'B';>
>I can't put two count distinct expressions in the same query with IDS
>7.3 which is what I'm using.
>
>I need to sort the result set on this funky key. If I the temp table
>costs to much, I suppose I can just as well sort it in the application.
>
>In some other vendors products I can use an implicit view, something
>like
>
>select a/b,b
> from (select count(f0.id) a,count(distinct f0.id) b
> from foo f0 where f0.val = 'A' or f0.val = 'B');
>
>Informix doesn't appear to support this syntax.
>
>Cheers
>