from SETs to Regular tables
Hugo Zambrano wanted to turn rows holding two SET columns into one row per set element, tagged r or a, but recursive CTE and subquery attempts failed (error -26088). Art Kagel posted a working query; Luis Marques gave a solution tested on 14.10FC5 using LATERAL with TABLE subqueries and UNION. Hugo confirmed it worked and adapted it with ORDER BY.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I want to convert the following table:
1 SET{10,11} SET{20,21}
2 SET{15} SET{30,31}
.
.
.
into this:
1 10 r
1 11 r
1 20 a
1 21 a
2 15 r
2 30 a
2 31 a
.
.
.
WITH ttt (sq, rejectedPinSet, acceptedPinSet) AS
(SELECT 1, SET{10,11}, SET{20,21} UNION ALL
SELECT 2, SET{15}, SET{30,31}
) SELECT * FROM ttt;The following fails because ord+1 should come from "test" table but "test" table is not mentioned in the second UNION portion
WITH test(ord, PIN, ra) AS
((SELECT 1, *, 'r' FROM TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = 1)) AS rej(pin) UNION
SELECT 1, *, 'a' FROM TABLE ((SELECT acceptedPinSet FROM ttt WHERE sq = 1)) AS acp(pin)
) UNION
(SELECT ord+1, *, 'r' FROM TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = ord+1)) AS rej(pin) UNION
SELECT ord+1, *, 'a' FROM TABLE ((SELECT acceptedPinSet FROM ttt WHERE sq = ord+1)) AS acp(pin) )
) SELECT * FROM test
In the following construct I replaced ord+1 for
(SELECT ord+1 FROM test)but fails with error -26088:
WITH test(ord, PIN, ra) AS
((SELECT 1, pin, 'r' FROM TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = 1)) AS rej(pin) UNION
SELECT 1, pin, 'a' FROM TABLE ((SELECT acceptedPinSet FROM ttt WHERE sq = 1)) AS acp(pin))
UNION ALL
(SELECT (SELECT ord+1 FROM test), pin, 'r' FROM TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = (SELECT ord+1 FROM test))) AS rej(pin) UNION
SELECT (SELECT ord+1 FROM test), pin, 'a' FROM TABLE ((SELECT acceptedPinSet FROM ttt WHERE sq = (SELECT ord+1 FROM test))) AS acp(pin))
) SELECT * FROM testThe following test fails too:
SELECT sq, pin
FROM ttt AS t1
JOIN TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = t1.sq)) AS rej(pin)
------------------------------
Zambrano, HugoInformix DBA
Ottawa Police
Ottawa, ON
(613)236-1222, 5575
------------------------------
#Informix
Like this:
dbaccess testdb -
> select 1,*,'r' from table( set{ 10,11} )
> union
> select 1,*,'a' from table( set{ 20,21 } )
> union
> select 2,*,'r' from table( set{15})
> union
> select 2,*,'a' from table( set {30,31})> ;
(constant) unnamed_col_1 (constant)
1 10 r
1 11 r
1 20 a
1 21 a
2 15 r
2 30 a
2 31 a 7
row(s) retrieved.
>
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Thanks Art, That’s the idea, to find a way to programmatically (with SQL statements) be able to do all those UNIONs applied to hundreds of records. ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------
Would something like this work for you ( tested on Informix 14.10FC5 )?
CREATE TABLE demo01
(
id INTEGER,
col_a SET ( INTEGER NOT NULL ),
col_b SET ( INTEGER NOT NULL )
);
INSERT INTO demo01 VALUES( 1, SET{ 10, 11 }, SET{ 20, 21 } );
INSERT INTO demo01 VALUES( 2, SET{ 15 }, SET{ 30, 31 } );----------------
SELECT * FROM demo01;id 1
col_a SET{10 ,11 }
col_b SET{20 ,21 }
id 2
col_a SET{15 }
col_b SET{30 ,31 }
----------------
SELECT vtab1.id AS ord, vtab2.pin AS pin, 'r' AS pinset FROM demo01 AS vtab1,LATERAL
(
TABLE( ( SELECT vtab3.col_a FROM demo01 AS vtab3 WHERE vtab1.id = vtab3.id ) )
) AS vtab2( pin )
UNION
SELECT vtab4.id AS ord, vtab5.pin AS pin, 'a' AS pinset FROM demo01 AS vtab4,LATERAL
(
TABLE( ( SELECT vtab6.col_b FROM demo01 AS vtab6 WHERE vtab4.id = vtab6.id ) )
) AS vtab5( pin )
ord pin pinset
1 10 r
1 11 r
1 20 a
1 21 a
2 15 r
2 30 a
2 31 a
------------------------------
Luis Marques
------------------------------
Gracias Luis, that was beautiful. It does work as required. I thought there was not going to exist an answer but you found it. I did see that LATERAL in the documentation but I could not used properly.
I just change a little bit the nomenclature for my understanding:
SELECT t1.sq, t2.pin AS pin, 'r' AS ra
FROMdemo01
AS t1,
LATERAL (TABLE((SELECT x1.rejectedPinSet FROM
demo01
AS x1 WHERE x1.sq = t1.sq))) AS t2(pin)
UNION
SELECT t3.sq, t4.pin AS pin, 'a' AS ra
FROMdemo01
AS t3,
LATERAL (TABLE((SELECT x2.acceptedPinSet FROM
demo01
AS x2 WHERE x2.sq = t3.sq ))) AS t4(pin)
ORDER BY sq, ra DESC, pin
Thanks a lot.
------------------------------
Zambrano, Hugo
Informix DBA
Ottawa Police
Ottawa, ON
(613)236-1222, 5575
------------------------------