That notation does not yet work in IDS -- it's high on my hitlist, but
not there yet.

I trust you mean 7.31 or later -- you should not be using 7.30 any
more; nor, indeed, should you be using anything less than 7.31.xD7
(released in January 2004).  Anything earlier is not good news.

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
))

This won't work in 7.31.

--
Jonathan Leffler <jonathan.leffler@gmail.com>
#include <disclaimer.h>
"I don't suffer from insanity - I enjoy every minute of it"

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

Besides...

Let say you call stored procedure for every row in a query, _and_ inside the stored procedure you create and drop a temporary table...

I had a lot of problems doing this, unitl someone give a solution to this by using exceptions...

I think no problem exist by using _derived tables_
.

Chucho!

-----Original Message-----
From: "rkusenet" <rkusenet@gmail.com>
.. 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...

Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com

sending to informix-list

Let the world know your discovery...

Chucho!

-----Original Message-----
From: rkusenet <rkusenet@gmail.com>
To: Jean Sagi <jeansagi@myrealbox.com>
Date: Thu, 23 Sep 2004 15:58:11 -0400
Subject: Re: Re: Re: Getting the number of rows returned by a select with group by.

Yes I believe there should be no problem if derived tables (TABLE(MULTISET)) is
used since technically we are not creating/dropping an object inside a SP.

I was forced to find other workarounds for the SP problem by using real tables
as global temp tables with SESSIONID column to link it to the session.
Much less
elegant than this.
However I am not sure how good the performance of derived tables. I will try to
do a bench mark.

Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com

sending to informix-list

What about the "select dbinfo('sqlca.sqlerrd2')" function? See page
4-134 of the SQL Syntax Guide version 9.2.

Brice Avila

Jean Sagi wrote

I think we may have this problem ...

Can you explain what you mean here please.

Colin Bull

________________________________________________________________________
This email has been scanned for all known viruses by the MessageLabs Email
Security System.
________________________________________________________________________

sending to informix-list

Well the problem I face some time ago ago was some kind of:

... sp_foo(...) ...
select ... into temp tx_foo with no log;
...
drop table tx_foo;
...

And when I ran a query like this:

select col_1,col_2,...,col_n, sp_foo(col_i,..., col_k)
from   some_table
;

Informix Online 5.xx (at that time) complains about as tx_foo already exists and couldn't be dropped.

The solution was to use exceptions and catch this exception _drop tx_foo_ and resume...

Well the quid of this is the fact that inside the stored procedure I create a temporary table for some purpose and when it was used it was discarded...

With a derived table like ...
 FROM TABLE( MULTISELECT (...))   -- IDS 9.x of course

I think I could avoid the problems I have, but of course, I'm jus speculating here, I have no prove of this.

Chucho!

-----Original Message-----
From: "Colin Bull" <Colin.Bull@videonetworks.com>
To: <informix-list@iiug.org>
Date: Thu, 30 Sep 2004 13:23:23 +0100
Subject: Re: Getting the number of rows returned by a select with group by.



Jean Sagi wrote

I think we may have this problem ...

Can you explain what you mean here please.

Colin Bull

________________________________________________________________________
This email has been scanned for all known viruses by the MessageLabs Email
Security System.
________________________________________________________________________

sending to informix-list

Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com

sending to informix-list