CODEWITHSUNDEEP
sqlsyntaxwhere-clauseinformix
CloudyMarble
CloudyMarble

Reputation: 37566

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.

Upvotes: 1

Views: 1946

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753665

You can alias columns in the select-list or tables (or sub-queries) in the FROM clause.

Therefore, you'd write:

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

or you'd write:

SELECT t.*, r.*
  FROM (SELECT ColA, ColB 
          FROM Table1 AS TestTable
         WHERE type = 'A'
       ) AS t
  JOIN AnotherTable AS r ON t.ColA = r.ColC

Note that in this query, the name TestTable does not actually get used. A more complex sub-query might well make use of an alias (though I have a strong preference for short aliases — aliases that are shorter than the table name that is aliassed).

Upvotes: 1

Samuel
Samuel

Reputation: 2490

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)

Upvotes: 2

Thomas
Thomas

Reputation: 64635

As far as I know, trying to use an alias in a Where clause is not permitted in any database system. You can push the results of a Select statement into a table using Select ... Into like so:

Select ColA, ColB Into Table2
From Table1 As TestTable

However, you would then need to use a separate SQL statement to use Table2 in the above example. The other choice is to use a common-table expression which I believe is supported by Informix:

With Foo As
    (
    Select ColA, ColB
    From Table1 As TestTable
    )
Select ColA, ColB
From Foo

Upvotes: 0

Related Questions