Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Selecting rows where tuple in condition

    Posted 13 days ago

    Hi,

    Using Informix v14.10, we want to select rows from a table using syntax similar to what other sql engines can do:

    • SELECT * FROM mytable
       WHERE (group_id, group_type) IN (('1234-567', 2), ('4321-765', 3), ('1111-222', 5) );
    • SELECT * FROM mytable
      WHERE (group_id, group_type) IN (
                                        VALUES ('1234-567', 2),
                                               ('4321-765', 3),
                                               ('1111-222', 5)
      );

    Both return syntax error in Informix

    One obviuos solution is to concatenate the column names and the value sets, but this does a secuential scan on the table not using the index on the columns, and we do not want to create a functional index.

    Thanks so much for any input provided.

    Jose



    ------------------------------
    Jose Manuel Ruiz Gallud
    ------------------------------


  • 2.  RE: Selecting rows where tuple in condition

    Posted 13 days ago

    If it were me, I would drop your WHERE clause IN items into a temp table and INNER JOIN to it.  You could also break each pair of IN items into a separate SELECT and UNION them together, it's ugly, but if you can't do the temp table, then it should work:

    SELECT * FROM mytable

    WHERE group_id = '1234-567'

                AND group_type = 2

    UNION

    SELECT * FROM mytable

    WHERE group_id = '4321-765'

                AND group_type = 3

    ...etc.

     

                                    --EEM

     




    Original Message


  • 3.  RE: Selecting rows where tuple in condition

    Posted 13 days ago

    an OR list of ANDed pairs probably would be cheaper, and a little less ugly.

    What Jose didn't tell us yet is how many such pairs there are - if many, then that temp table approach would be clearer anyway?



    ------------------------------
    Andreas Legner
    Informix Dev
    HCL Software
    ------------------------------

    Original Message


  • 4.  RE: Selecting rows where tuple in condition

    Posted 9 days ago

    Hi,

    Thanks for all the answers. 

    -. There can be up to 20 different tuples.

    Using  SELECT * FROM clinumas a WHERE (row(a.prvasist, a.numasist)) IN ( row(14, 1008001), row(21, 18), row(14, 1003844) );

    works but does not use the index created on (prvasist, numasist), so it takes 431 secs to resolve query. Table has 17979930 rows.

    The best options we have tested that are using the index (resolve in 0.02 and 0.05 secs) are:

    1. SELECT * FROM clinumas a WHERE (prvasist = 14 AND a.numasist = 1008001) OR (prvasist = 21 AND  a.numasist = 18) OR (prvasist = 14 AND a.numasist = 1003844);

    2. CREATE TEMP TABLE tt1 (col1 int, col2 int); INSERT INTO tt1 VALUES (14, 1008001); INSERT INTO tt1 VALUES (2, 18); INSERT INTO tt1 VALUES (14, 1003844); SELECT *
      FROM clinumas a , tt1 b WHERE a.prvasist = b.col1 and  a.numasist = b.col2;

    The first option is simpler if few tuples are used, the second option (temp table) recommended if hundreds or thousands of OR's have to be used.

    BTW, we do not have multivalue insert statement in Informix, do we?

    • (like  INSERT INTO EEEE (id_empleado, nombre, apellido)
          VALUES (1, 'Juan', 'Pérez'),
                 (2, 'Maria', 'Gómez'),
                 (3, 'Pedro', 'López');

    Thanks!,

    Jose.



    ------------------------------
    Jose Manuel Ruiz Gallud
    ------------------------------

    Original Message


  • 5.  RE: Selecting rows where tuple in condition

    Posted 13 days ago

    Jose:

    Try this:

    SELECT * FROM mytable
     WHERE (ROW( group_id, group_type ))
         IN ( ROW('1234-567', 2), ROW('4321-765', 3), ROW('1111-222', 5) );

    The syntax is accepted, I just get table not found when I try this, obviously.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 6.  RE: Selecting rows where tuple in condition

    Posted 10 days ago
    Art,
    This is the coolest SQL feature I've seen in a while.  But the troublemaker in me has a question: What if the IN clause is really a subquery?

    I suppose the syntax would look something like this:
    SELECT * FROM mytable
     WHERE ROW( group_id, group_type )
        IN ( SELECT(gid, gtype) from other_table)
    ;

    Or maybe:
    SELECT * FROM mytable
     WHERE ROW( group_id, group_type )
        IN ( SELECT ROW(gid, gtype) from other_table)
    ;


    I have no place to try this but I had to bring it up. I suspect the nail will come up, now that you have given us this hammer.

    Thoughts?

    • Jacob S




    Original Message


  • 7.  RE: Selecting rows where tuple in condition

    Posted 10 days ago

    Jacob:

    Your second version works just fine:

    > create table row_in_in_1( one serial, two char(5), three char(5) );
     
    Table created.
     
    > create table row_in_in_2( one serial, two char(5), three char(5) );
     
    Table created.
     
    > insert into row_in_in_1 values (0,'fred', 'wilma' );
     
    1 row(s) inserted.
     
    > insert into row_in_in_1 values (0,'barny', 'betty' );
     
    1 row(s) inserted.
     
    > insert into row_in_in_2 select * from row_in_in_1;
     
    2 row(s) inserted.
     
    > insert into row_in_in_1 values (0,'art','claire');
     
    1 row(s) inserted.
     
    > select * from row_in_in_1
    > where row(two, three) in ( select row(two, three) from row_in_in_2 );
     
     
            one two   three 
     
              1 fred  wilma
              2 barny betty
     
    2 row(s) retrieved.
    >
     select * from row_in_in_1;
     
     
            one two   three 
     
              1 fred  wilma
              2 barny betty
              3 art   clair
     
    3 row(s) retrieved.
     
    > 
    Art


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


Related Content