Select hardcoded values in Informix DB

Question

I need to select hardcoded values in one column, so I will be able to join them with table in Informix DB. So I try in different variations to do something like this:

select a from ( values (1), (2), (3) ) ;

And I expect to get results:

1
2
3

I think in other DB this or some other variations that I tried would return the values. However, in Informix it does not work.

Could anyone suggest the solution working in Informix please?

Answer 1

Although what Gordon Linoff suggests will certainly work, there are also more compact notations available using Informix-specific syntax.

For example:

SELECT a
  FROM TABLE(SET{1, 2, 3}) AS t(a)

This will generate a list of integers quite happily (and succinctly). You can use LIST or MULTISET in place of SET. A MULTISET can have repeated elements, unlike a SET; a LIST preserves order as well as allowing repeats.

Very often, you won't spot order not being preserved with simple values — just a few items in the list. Order is not guaranteed for SET or MULTISET; if order matters, use LIST.

You can find information about this in the IBM Informix 12.10 manual under Collection Constructors. No, it isn't obvious how you get to it — I started at SELECT, then FROM, then 'Selecting from a collection variable' and thence to 'Expression'; I spent a few seconds staring blankly at that, then looked at 'Constructor expressions' and hence 'Collection Constructors'.