Select hardcoded values in Informix DB
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'.
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
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;