Selecting rows where tuple in condition
Jose Manuel Ruiz Gallud wanted tuple IN syntax on 14.10, which gave syntax errors. Everett Mills suggested a temp table join or UNION; Art Kagel showed ROW(a,b) IN (ROW(...)) works, also with a ROW subquery per Jacob Salomon. Jose found it did a full scan (431s), while an OR list of ANDed pairs or a temp table join used the index (0.02-0.05s). He also asked about multi-row VALUES; unanswered.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
------------------------------
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
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 ------------------------------
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
------------------------------
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 ( SELECTROW
(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
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
------------------------------
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:
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);
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
------------------------------