Re: SQL construct not supported in informix ?

Intereting... Does it work in IDS 9.3 too? I think the same could be achieved by creating a temporary table: select col1 as a, col2 as b from mytable into temp tx with no log; select a,b from tx; Questions: - Wich is better? - Why I may want to use a FROM like that? Chucho! Kristofer Andersson wrote: > vk02720@my-deja.com (vk02720) wrote in message news:<4d814faa.0307170740.3e983552@posting.google.com>... > >>The following SQL construct does not work in Informix 7.3 >>select a,b from >>(select col1 a, col2 b from mytable)>> >>This works in Oracle. >>Is this part of SQL standard or an Oracle extension ? >> >>TIA > > > > You can achieve the same result by doing: select a, b from > Table(Multiset(select col1 as a, col2 as b from mytable)) as foo > > However, some other databases would try to expand the subquery and > find more efficient joins to table in the outer query if there are > any. Informix won't. > > -- Atte, Jes's Antonio Santos Giraldo jeansagi@myrealbox.com jeansagi@netscape.net sending to informix-list

Jean Sagi <jeansagi@myrealbox.com> wrote in message news:<bfj7tu$dre$1@terabinaries.xmission.com>... > Intereting... > > Does it work in IDS 9.3 too? Yes, I have used it on 9.3 and 9.4. > I think the same could be achieved by creating a temporary table: > > select col1 as a, col2 as b from mytable into temp tx with no log; > select a,b from tx; Yes, the "table(multiset(" will usually (or maybe even always?) result in a temp table. > Questions: > > - Wich is better? Probably a matter of taste. > - Why I may want to use a FROM like that? In the example in the previous post there is no reason. But if you want to join sets of aggregated data, it is very useful, or filter down the results before joining. Don't use it if you don't have to.