Re: Re: Getting the number of rows returned by a select with group by.
Hey,
This works on 9.3, !! Really cool!
Usually I insert the result into a temporary table and then count it or If using 4gl I access sqlca or if using dbaccess i unload to a temporary file and look for the number of unloaded rows...
Sorry it doesn't work on 7.31...
Chucho!
-----Original Message-----
From: jonathan.leffler@gmail.com
Do you mean that the solution must work in 7.31 and with 9.x, or is a
solution that works only in 9.x permitted?
SELECT COUNT(*) FROM
TABLE(MULTISET(
SELECT a,b,c,COUNT(*) FROM x GROUP BY a,b,c HAVING COUNT(*) > 1
))
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
sending to informix-list
"Jean Sagi" <jeansagi@myrealbox.com> wrote in message news:civ3ov$np5$1@news.xmission.com...
>
>
> Hey,
>
> This works on 9.3, !! Really cool!
>
> Usually I insert the result into a temporary table and then count it or If using 4gl I access
sqlca or if using dbaccess i unload to a temporary file and look for the number of unloaded rows...
>
> Sorry it doesn't work on 7.31...
works in 9.21 too.
Wow !!! this is COOOOOOOL.
I never thought I could clone DERIVED tables (as it is called in SQL Server)
using TABLE (MULTISET).
this code works.
SELECT ZC.KEY
FROM ZONE_CITY ZC,TABLE (MULTISET(SELECT airportcode
FROM city
WHERE citycode=ANY(SELECT citycode FROM city WHERE airportcode='JFK')
)
) T
WHERE ZC.DEST = T.AIRPORTCODE ;
until now I had to break it into temp table to achieve the same. The problem comes in
stored procedures where creation/destruction of a temp table inside the SP forces
the engine to recompile, which in turn puts a lock on sysprocplan.
The above solution can easily eliminate the use of temp tables.
Thanks Jonathan.