Subselect in a Select Statement
Informix does have sub selects but I don't think you can use them in
place of tables. Never felt the need to, I don't think. Try the
following;
**This is if you are storing year an Integer**
select a.client_id, a.savings, b.savings, c.savings
from s1 a, s2 b, s3 c
where a.client_id = b.client_id
and b.client_id = c.client
and a.year = 2001
and b.year = 2002
and c.year = 2003
I don't see the need for a sub select but maybe I have missed it.
>Subject: Subselect in a Select Statement
>From: eyebyte@yahoo.com (Joe)
>Newsgroups: comp.databases.informix
>Date: 7 Apr 2004 08:41:52 -0700
>
>I am fairly new to informix sql. I know this works in db2 and udb. But
>I don't know how to do it in informix. It seems it does not have a
>subselect
>select a.client_id, a.savings, b.savings, c.savings
>from
>(select s1.client_id, savings from s1 where s1.year = 2001) as a,,
>(select s2.client_id, savings from s2 where s2.year = 2002) as b,
>(select s3.client_id, savings from s3 where s3.year = 2003) as c,
>where a.client_id = b.client_id
> and b.client_id = c.client
>How could I get around it? TIA
sending to informix-list
sending to informix-list
Sorry I didn't state before. This would work if you have a saving on
all those years. What would happen if you have savings on 2002 and
2003 only and 2001 you don't have? Maybe I should use Full Outer join
For example I have this table
john doe 2001 $3000
john doe 2002 $2000
john smith 2001 $25
john smith 2002 $25
john smith 2003 $25
I want to create select that would show a cross tab
name year 2001 saving for 2001 year 2002 saving for 2002
...so on
john doe 2001 $3000 2002 $2000
john smith 2001 $25 2002 $25 2003
$25
Eric Rowell <erowell@knology.net> wrote in message news:<c51ecf$1jv$1@terabinaries.xmission.com>...
> Informix does have sub selects but I don't think you can use them in
> place of tables. Never felt the need to, I don't think. Try the
> following;
>
> **This is if you are storing year an Integer**
>
> select a.client_id, a.savings, b.savings, c.savings
> from s1 a, s2 b, s3 c
> where a.client_id = b.client_id
> and b.client_id = c.client
> and a.year = 2001
> and b.year = 2002
> and c.year = 2003>
> I don't see the need for a sub select but maybe I have missed it.
>
> >Subject: Subselect in a Select Statement
> >From: eyebyte@yahoo.com (Joe)
> >Newsgroups: comp.databases.informix
> >Date: 7 Apr 2004 08:41:52 -0700
> >
> >I am fairly new to informix sql. I know this works in db2 and udb. But
> >I don't know how to do it in informix. It seems it does not have a
> >subselect
>
> >select a.client_id, a.savings, b.savings, c.savings
> >from
> >(select s1.client_id, savings from s1 where s1.year = 2001) as a,,
> >(select s2.client_id, savings from s2 where s2.year = 2002) as b,
> >(select s3.client_id, savings from s3 where s3.year = 2003) as c,
> >where a.client_id = b.client_id
> > and b.client_id = c.client>
> >How could I get around it? TIA
>
>
> sending to informix-list
> sending to informix-list