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?

Alkalinity

Answer 1

Informix requires an actual query statement. I think this will work:

select a
from (select 1 as a from systables where tabid = 1 union all
      select 2 as a from systables where tabid = 1 union all
      select 3 as a from systables where tabid = 1
     ) t;

Swatter

Answer 2

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'.

Entertaining

Answer 3

INSERT INTO cccmte_pp ( cmte, pref, nro, eje, id_tri, id_cuo, fecha, vto1, vto2, id_tit, id_suj, id_bie, id_gru )
SELECT *
  FROM TABLE (MULTISET {
    row('RC', 4, 10, 2020, 1, 5, MDY(05,20,2020), MDY(05,20,2020),MDY(05,27,2020),101, 1, 96, 1 ), 
    row('RC', 4, 11, 2020, 1, 5, MDY(05,20,2020), MDY(05,20,2020),MDY(05,27,2020),101, 1, 96, 1 ) 
    }) 
    AS t( cmte, pref, nro, eje, id_tri, id_cuo, fecha, vto1, vto2, id_tit, id_suj, id_bie, id_gru )

IS SIMPLE SOLUTION FOR BULK INSERT, and SELECT PART SOLVING THE REST! IS VERY SIMPLE! :) ENJOY

Coaming

This is so beatiful, I'll be always using it from now on. – Batho