FROM TABLE(MULTISET(SELECT ...))

or something like that.

You have to tell Informix that a) a select statement is a bag,
and b) you can make a table from it.

My good...

That select isn't indented...

Well I suppose that the interim select could be extracted into temporary tables. I think that is what Informix does not support...

The left outer join is supported.

Chucho!

-----Original Message-----
From: fprose@hotmail.com (Fred Prose)
To: informix-list@iiug.org
Date: 19 Nov 2003 08:22:28 -0800
Subject: Support of sql-92 select statement

We've been given the SQL source code for an application written to
sql-92 standards.  Some of the syntax looks like this:

select
       c_count,
       count(*) as custdist
from
       (
               select
                       c_custkey,
                       count(o_orderkey)
               from
                       customer left outer join orders on
                               c_custkey = o_custkey
                               and o_comment not like
'%express%deposits%'
               group by
                       c_custkey
       ) as c_orders (c_custkey, c_count)
group by
       c_count
order by
       custdist desc,
       c_count desc;

Correct me if I'm wrong, but I don't see that IDS 9.x supports this?

Fred Prose
Arizona Supreme Court

Jean Sagi
jeansagi@myrealbox.com
jeansagi@netscape.net

sending to informix-list

Very interesting...

But,

Have this approach performance penalties?

Chucho!

Richard Harnden wrote:

Obnoxio The Clown wrote:

;)

I think the same...

Chucho!

XPS is supporting this syntax.

Andreas