# Informix

View Only

## from SETs to Regular tables

• #### 1.  from SETs to Regular tables

Posted 11 days ago
Edited by Hugo Zambrano 10 days ago
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 test`

The following test fails too:
`SELECT sq, pinFROM ttt AS t1JOIN TABLE ((SELECT rejectedPinSet FROM ttt WHERE sq = t1.sq)) AS rej(pin)`

------------------------------
Zambrano, Hugo
Informix DBA
Ottawa Police
Ottawa, ON
(613)236-1222, 5575
------------------------------

• #### 2.  RE: from SETs to Regular tables

Posted 10 days ago
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
------------------------------

• #### 3.  RE: from SETs to Regular tables

Posted 9 days ago
Edited by Hugo Zambrano 9 days ago
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
------------------------------

• #### 4.  RE: from SETs to Regular tables Best Answer

Posted 3 days ago
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 1col_a SET{10 ,11 }col_b SET{20 ,21 }id 2col_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
------------------------------

• #### 5.  RE: from SETs to Regular tables

Posted yesterday
Edited by Hugo Zambrano yesterday
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    FROM demo01 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    FROM demo01 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
------------------------------