SELECT output to SETs and from SETs to Regular tables
Hugo Zambrano wanted to build SET columns from SELECT results and read them back (Informix 12.x). Luis Marques showed that a collection from a SELECT must be a MULTISET, then cast to SET with ITEM and a FROM clause such as systables where tabid = 1; the cast placement matters or data is corrupted. Hugo confirmed it worked. Adding or removing elements needs ESQL/C or SPL, as no plain SQL was found.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I have not been able to completely understand how SETs work. I want to create SETs from not-singlet results of SELECT statements and I want to do the oposite, from SETs to regular table-like outputs.
DROP TABLE IF EXISTS persMergeExcl_tmp;
CREATE TEMP TABLE IF NOT EXISTS persMergeExcl_tmp
( rejectedPinSet SET(INT NOT NULL),
acceptedPinSet SET(INT NOT NULL)
);The following INSET works:
INSERT INTO persMergeExcl_tmp VALUES
(SET{10,11,12},
SET{20,21,22,23,24});The following INSERT does not work but shows the idea I want to achieve. There is always going to be a low number of items (no more than 15) in the rejectedPinSet and acceptedPinSet SETs.
INSERT INTO persMergeExcl_tmp VALUES
(SET{(SELECT rowid FROM t1 WHERE rowid < 260)},
SET{(SELECT rowid FROM t2
WHERE rowid < 780)});Latter on I want to achieve things like this:
SELECT acceptedPinSet FROM persMergeExcl_tmp WHERE rejectedPinSet has value nnn
------------------------------
Zambrano, HugoInformix DBA
Ottawa Police
Ottawa, ON
(613)236-1222, 5575
------------------------------
#Informix
Using Informix 14.10, I can do the following.
I have to create a MULTISET and then CAST to SET. I have to use the keyword ITEM, so the MULTISET is not composed of unnamed ROW types.
INSERT INTO ops_persmergeexcl_tmp
VALUES
(
( SELECT ( MULTISET( SELECT ITEM rowid FROM t1 WHERE rowid < 260 ) )::SET( INT NOT NULL ) ),
( SELECT ( MULTISET( SELECT ITEM rowid FROM t1 WHERE rowid < 780 ) )::SET( INT NOT NULL ) )
);
Be careful with the the CAST from MULTISET . If I instead do this:
( SELECT ( MULTISET( SELECT ITEM rowid FROM test_t1 WHERE rowid < 780 ) ) )::SET( INT NOT NULL )
It will corrupt the SET data ( not sure why ).
Then you can do
SELECT acceptedpinset FROM persmergeexcl_tmp WHERE valuennn IN rejectedpinset;------------------------------
Luis Marques
------------------------------
Thanks. Your MULTISET-cast-SET solution is throwing a syntax error, perhaps it is because I have Informix 12.x. Informix 14 will take months to come. Is there any other way? ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------
I forgot version 12.10 does not support the "SELECT 1" type of sintax, as it always requires an explicit "FROM" clause.
Try the following:
INSERT INTO ops_persmergeexcl_tmp
VALUES
(
( SELECT MULTISET( SELECT ITEM rowid FROM t1 WHERE rowid < 260 )::SET( INT NOT NULL ) FROM systables WHERE tabid = 1 ),
( SELECT MULTISET( SELECT ITEM rowid FROM t1 WHERE rowid < 780 )::SET( INT NOT NULL ) FROM systables WHERE tabid = 1 )
)
Again, be careful with the position of the CAST to the SET type. Placed in the wrong order it will corrupt the data in the SET.
------------------------------Luis Marques
------------------------------
Yes, It did worked. I did not used systoles but another table I have that I know always have one row. Thanks. I wander why I need to use MULTISET and then cast it into a SET, why I cannot use SET in the first place. How would I add one or more elements to a SET column or remove an element? ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------
According to the online documentation on the IBM site, creating a collection from a SELECT can only be done for a MULTISET. So we create a MULTISET and them cast to SET, since there is an Informix cast operator for such transformation. As for manipulating SET columns , it appears it cannot be done with just declarative SQL ( from what I could find ). It can be done using ESQL/C or a SPL routine. Basically you fetch the SET value you want to change into a temp variable, change it, and them replace the original SET on the table with the updated version. ------------------------------ Luis Marques ------------------------------
Thanks a lot !! ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------