select where (a,b) in (select a,b)...
I know that I can do it simply joining the tables, but I'm just curious
about this. If I do (and I believe that I had done this in Oracle):
select *
from sched_d
where (sched_date, route_code) in
(select sched_date, route_code
from sched_h
where sched_date >= today
and confirmed = 'N')
I get a syntax error, but
select *
from sched_d
where (sched_date || route_code) in
(select sched_date || route_code
from sched_h
where sched_date >= today
and confirmed = 'N')
It works, but it's very slow because, despite of the fact that I've and
index in sched_d with sched_date and route_code, it's not used.
Remember, it's just curiosity. I don't want to perform a join or a sub-
select. I have Informix Dynamic Server Version 7.31.UC2
Thanks for your comments
Sent via Deja.com http://www.deja.com/
Before you buy.
On Tue, 07 Mar 2000 16:12:36 GMT, mdaponte@prtc.net wrote:
>select *
> from sched_d
> where (sched_date, route_code) in
> (select sched_date, route_code
> from sched_h
> where sched_date >= today
> and confirmed = 'N')
Its been requested, and it would be nice to have,
but unless you are a very big customer of Informix,
don't hold your breath.
Cheers,
Douglas Wilson
This is a case where the SQL STANDARD 'EXISTS' would work well and generally
perform excellently.
Doug Agnew
"Douglas Wilson" <dwilson@gtemail.net> wrote in message
news:38c4ac1f.225846@news...
> On Tue, 07 Mar 2000 16:12:36 GMT, mdaponte@prtc.net wrote:
>
> >select *
> > from sched_d
> > where (sched_date, route_code) in
> > (select sched_date, route_code
> > from sched_h
> > where sched_date >= today
> > and confirmed = 'N')>
> Its been requested, and it would be nice to have,
> but unless you are a very big customer of Informix,
> don't hold your breath.
>
> Cheers,
> Douglas Wilson
Doug Agnew wrote:
> This is a case where the SQL STANDARD 'EXISTS' would work well and generally
> perform excellently.
And, the query would look like this :
select * from sched_d d
where exists (
select 1 from sched_h h
where d.sched_date = h.sched_date
and d.route_code = h.route_code
and h.confirmed = 'N')
and sched_date >= today; -- Better to keep this on sched_d
An index on sched_h(sched_date, route_code) would help enormously.
Rudy
> Doug Agnew
>
> "Douglas Wilson" <dwilson@gtemail.net> wrote in message
> news:38c4ac1f.225846@news...
> > On Tue, 07 Mar 2000 16:12:36 GMT, mdaponte@prtc.net wrote:
> >
> > >select *
> > > from sched_d
> > > where (sched_date, route_code) in
> > > (select sched_date, route_code
> > > from sched_h
> > > where sched_date >= today
> > > and confirmed = 'N')> >
On Tue, 07 Mar 2000 14:25:49 -0500, Rudy Fernandes <rferdy@americasm01.nt.com>
wrote:
>Doug Agnew wrote:
>
>> This is a case where the SQL STANDARD 'EXISTS' would work well and generally
>> perform excellently.
Except that its ugly.
>
>And, the query would look like this :
>
>select * from sched_d d
>where exists (
> select 1 from sched_h h
> where d.sched_date = h.sched_date
> and d.route_code = h.route_code
> and h.confirmed = 'N')
>and sched_date >= today; -- Better to keep this on sched_d
Compare that to:
select *
from sched_d
where (sched_date, route_code) in
(select sched_date, route_code
from sched_h
where sched_date >= today
and confirmed = 'N')
I think the latter is more intuitive, elegant, etc., and that
it ought to be standard, IMHO. Two points for Oracle (or
any other database) if it really does support that syntax
(like the original poster said it might).
And if sched_date, route_code is a unique composite index
in the header table, then there's always:
select d.*
from sched_h h, sched_d d
where h.sched_date=d.sched_date
and h.route_code=d.route_code
and h.sched_date >= today
and h.confirmed = 'N'
Cheers,
Douglas Wilson