Informix

 View Only

  • 1.  SELECT output to SETs and from SETs to Regular tables

    Posted Wed September 16, 2020 05:32 PM
    Edited by System Fri January 20, 2023 04:09 PM
    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, Hugo
    Informix DBA
    Ottawa Police
    Ottawa, ON
    (613)236-1222, 5575
    ------------------------------
    #Informix


  • 2.  RE: SELECT output to SETs and from SETs to Regular tables

    Posted Thu September 17, 2020 08:28 AM
    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
    ------------------------------

    Original Message


  • 3.  RE: SELECT output to SETs and from SETs to Regular tables

    Posted Thu September 17, 2020 10:34 AM
    Edited by System Fri January 20, 2023 04:36 PM
    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
    ------------------------------

    Original Message


  • 4.  RE: SELECT output to SETs and from SETs to Regular tables
    Best Answer

    Posted Thu September 17, 2020 11:01 AM
    Edited by System Fri January 20, 2023 04:12 PM
    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
    ------------------------------

    Original Message


  • 5.  RE: SELECT output to SETs and from SETs to Regular tables

    Posted Thu September 17, 2020 11:47 AM
    Edited by System Fri January 20, 2023 04:09 PM
    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
    ------------------------------

    Original Message


  • 6.  RE: SELECT output to SETs and from SETs to Regular tables

    Posted Thu September 17, 2020 12:21 PM
    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
    ------------------------------

    Original Message


  • 7.  RE: SELECT output to SETs and from SETs to Regular tables

    Posted Thu September 17, 2020 01:43 PM
    Thanks a lot !!

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

    Original Message