Search code examples
sqlsyntaxwhere-clauseinformix

Combining Table Aliases with "Where" in Informix

I'm facing serious problems using table aliases on Informix with a where part.

So I can run: 

SELECT ColA, ColB 
  FROM Table1 AS TestTable

But as soon as i try this:

SELECT ColA, ColB 
  FROM Table1 
 WHERE type = 'A' AS TestTable

..it wouldn't work. The same happens when I try to join tables with a WHERE part.

I'm trying to optimize a query from this Question so I'm trying not to work with temporary tables.

Solution

  • I assume you are trying to get the equivalent of Oracle's inline views. Syntax is: 

    select *
from  
   TABLE(MULTISET(SELECT ColA, ColB 
  FROM Table1 
 WHERE type = 'A' )) T1
join 
   TABLE(MULTISET(SELECT ColA, ColB 
  FROM Table2 
 WHERE type = 'Z' )) T2 on (t2.colA = t1.colA)