Subselect in a Select Statement

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 - J

"Joe" <eyebyte@yahoo.com> wrote in message news:1641720c.0404070741.6505aad6@posting.google.com... > 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 You have to say, "table(multiset(select ...))" Provided you're on a 9.x, that is. But, what's wrong with: select s1.client_id, s1.savings, s2.savings, s3.savings from s1, s2, s3 where s1.client_id = s2.client_id and s2.client_id = s3.client_id and s1.year = 2001 and s2.year = 2002 and s3.year = 2003 ?