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:
Sent: Mon June 09, 2025 09:50 AM
From: Jacob Salomon
Subject: Selecting rows where tuple in condition
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?
Original Message:
Sent: 6/6/2025 12:18:00 PM
From: Art Kagel
Subject: RE: Selecting rows where tuple in condition
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:
Sent: Fri June 06, 2025 11:39 AM
From: Everett Mills
Subject: Selecting rows where tuple in condition
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:
Sent: 6/6/2025 8:08:00 AM
From: Jose Manuel Ruiz Gallud
Subject: Selecting rows where tuple in condition
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
------------------------------