3

I am trying a query similar to the below one on Informix, but I get a syntax error (-201: "A syntax error has occurred") every time. 

with a_qry (locationnames) as (SELECT * FROM TABLE(LIST{'abc','xyz'}))
select locationnames from a_qry;

Can someone please help with this?

Improve this question
3
  • 3
    And the syntax error says?
    – jarlh
    Commented Mar 3, 2017 at 13:13
  • 1
    Informix does not support the WITH sintaxe, used for CTE's. Maybe similar results can be obtained with derived tables or using CONNECT BY ?
    – Luís Marques
    Commented Mar 3, 2017 at 14:02
  • @jarlh: It gets the non-informative '-201: A syntax error has occurred' message. That's Informix's general "I've got no idea what's going on" message.
    – Jonathan Leffler
    Commented Mar 3, 2017 at 15:04

2 Answers 2

Reset to default
6

Informix 14.10 and later

Informix 14.10 (released in March 2019) added support for the WITH statement (Common Table Expressions).

Informix 12.10 and earlier

The documentation for the Informix SELECT statement in Informix 12.10 (which was the latest when this question was asked) does not include the WITH clause because the server does not support the WITH clause and common table expressions (CTEs) — a grievous omission, but nevertheless a fact of life.

For your specific example, you could use:

SELECT locationnames
  FROM (SELECT * FROM TABLE(LIST{'abc','xyz'})(locationnames));

which would yield:

abc
xyz

though the sub-query isn't necessary here, of course (you could simply use SELECT * FROM TABLE(LIST{'abc','xyz'})(locationnames) to get the same result). In general, though, you'll have to write out each reference to a CTE in full, with the consequential risk that the optimizer doesn't spot the commonality and therefore doesn't optimize as well as it might if it could.

Improve this answer
2

You can try to use a temp table (which doesn't require special permission).

select * from (your query)
into temp myTmpTable;

select * from MyTmpTable;
Improve this answer