Re: select from (select)

rakesh_sa@xxxxxxxxx wrote:
Hi Prateek,

Please see to the query mentioned below.This query works fine in oracle
& not in informix.
Any hints highly appretiated.

select a.col1 , b.col2 from
(select column1 col1 from table1) a,
(select column2 col2 from table2) b,
where a.col1 = b.col2

Thanks
Rakesh.

<SNIP>

Prateek:

In the general case Jonathan's proposal of using the 9.xx+ syntax: TABLE(MULTISET(SELECT...)) is the equivalent of the syntax above, however, ALL of the 'FROM (select..)' constructs can be produced using either temp tables or views and most can be implemented as simple joins. Also the TABLE and MULTISET casts are not available in earlier releases of IDS. So here are the options as I see them:

Using temp tables:

select column1 col1 from table1 into temp a;
select column2 col2 from table2 into temp b;

select a.col1, b.col2
from a, b
where a.col1 = b.col2;

drop table a;
drop table b;

Another solution using views:

create view first(col1) as select column1 col1 from table1;
create view second(col2) as select column2 col2 from table2;

select a.col1, b.col2
from first a, second b
where a.col1 = b.col2;

drop view first;
drop view second;

-- OR a bit less dramatically using a simple join --

select a.column1 as col1, b.column2 as col2
from table1 a, table2 b
where col1 = col2;

-- OR using ANSI syntax --

select a.column1 as col1, b.column2 as col2
from table1 a
join table2 b
on col1 = col2;

Art S. Kagel
.

Relevant Pages

  • Re: Help needed with importing XML
    ... You could add a gratuitous identity column to #temp and use ... Those examples showed how to decompose arbitrary XML in multiple ... table1 and table2. ... But there has to be something in the relational schema tying table1 ...
    (microsoft.public.sqlserver.xml)
  • Re: Q re Subqueries (Join Predicate) in Access
    ... FROM Table1 t1 ... WHERE Col2 =3 ... Note the full stop following the closing square bracket. ... select key from table2 ...
    (microsoft.public.access.queries)
  • Re: Help needed with importing XML
    ... or more than one document in the XML. ... I've done it in two steps (#temp table) to ... CREATE TABLE table1 ( ... CREATE TABLE table2 ( ...
    (microsoft.public.sqlserver.xml)
  • Update 2 tables with temp table
    ... import the data from Excel into a Temp table in Access. ... Now I need to update the information from Temp table to both Table1 and ... Table2, preferably via VBA or some sort of coding. ... database structure. ...
    (microsoft.public.access.queries)
  • Re: Help needed with importing XML
    ... I've done it in two steps (#temp table) to ... CREATE TABLE table1 ( ... CREATE TABLE table2 ( ... by step example that will import the following xml file into two tables ...
    (microsoft.public.sqlserver.xml)
Loading