Informix

Expand all | Collapse all

from SETs to Regular tables

Jump to Best Answer
  • 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, pin
    FROM ttt AS t1
    JOIN 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
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 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 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
    ------------------------------



  • 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
    ------------------------------