Help Outer Join!

I have trouble working with informix Outer join ! I want to change oracle sql to informix sql! but can not get the same result! anyone can help me get out it? in oracle sql>select * from aaa;a1 a2 1 abcd 2 abcd sql>select * from bbb;b1 b2 1 T 2 F sql>select * from aaa,bbb where a1=b1(+) and b2<>'T';a1 a2 b1 b2 2 abcd 2 F sql>select * from aaa,bbb where a1=b1(+) and b2='G';no rows selected in informix for the same tables select * from aaa,outer bbb where a1=b1 and b2<> 'T'; 1 abcd 2 abcd 2 F select * from aaa,outer bbb where a1=b1 and b2 <> 'G'; 1 abcd 1 T 2 abcd 2 F why?how to write the sql for outer join in informix to get the same result as that in oracle ! Sent via Deja.com http://www.deja.com/ Before you buy.

I've never used Oracle but in Informix SQL outer joins are simply SELECT * FROM aaa, OUTER bbb WHERE aaa.a1 = bbb.b1 -- aaa and bbb are not necessary here AND {any other conditions} The example you've quoted for informix looks correct to me. The output of the third SQL in your post looks like a normal join to me. -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . cnwy@my-deja.com wrote in message <86jv4g$46a$1@nnrp1.deja.com>... >I have trouble working with informix Outer join ! >I want to change oracle sql to informix sql! >but can not get the same result! >anyone can help me get out it? > >in oracle >sql>select * from aaa;>a1 a2 >1 abcd >2 abcd > > >sql>select * from bbb;>b1 b2 >1 T >2 F > >sql>select * from aaa,bbb where a1=b1(+) and b2<>'T';>a1 a2 b1 b2 >2 abcd 2 F > >sql>select * from aaa,bbb where a1=b1(+) and b2='G';>no rows selected > >in informix for the same tables >select * from aaa,outer bbb where a1=b1 and b2<> 'T';> >1 abcd >2 abcd 2 F > >select * from aaa,outer bbb where a1=b1 and b2 <> 'G';> >1 abcd 1 T >2 abcd 2 F > >why?how to write the sql for outer join in informix to get >the same result as that in oracle ! > > > >Sent via Deja.com http://www.deja.com/ >Before you buy.

Informix is returning the correct, though obviously not the expected, results. You can do what you want in a single query if you have Informix vers 7.3x or 9.2x or higher using ANSI '92 outer join syntax: select * from aaa, outer bbb join aaa.a1 = bbb.b1 and bbb.b2 != 'T' where bbb.b1 IS NOT NULL; This will not work with the older ANSI outer join syntax so if you have an earlier version that does not support this syntax you have to select the outer join to a temp table then select the rows from the temp table WHERE b1 IS NOT NULL. If you need to can this as a single query make it a stored procedure. Art S. Kagel cnwy@my-deja.com wrote: > I have trouble working with informix Outer join ! > I want to change oracle sql to informix sql! > but can not get the same result! > anyone can help me get out it? > > in oracle > sql>select * from aaa;> a1 a2 > 1 abcd > 2 abcd > > sql>select * from bbb;> b1 b2 > 1 T > 2 F > > sql>select * from aaa,bbb where a1=b1(+) and b2<>'T';> a1 a2 b1 b2 > 2 abcd 2 F > > sql>select * from aaa,bbb where a1=b1(+) and b2='G';> no rows selected > > in informix for the same tables > select * from aaa,outer bbb where a1=b1 and b2<> 'T';> > 1 abcd > 2 abcd 2 F > > select * from aaa,outer bbb where a1=b1 and b2 <> 'G';> > 1 abcd 1 T > 2 abcd 2 F > > why?how to write the sql for outer join in informix to get > the same result as that in oracle ! > > Sent via Deja.com http://www.deja.com/ > Before you buy.

> in oracle > sql>select * from aaa;> a1 a2 > 1 abcd > 2 abcd > > sql>select * from bbb;> b1 b2 > 1 T > 2 F > > sql>select * from aaa,bbb where a1=b1(+) and b2<>'T';> a1 a2 b1 b2 > 2 abcd 2 F > > sql>select * from aaa,bbb where a1=b1(+) and b2='G';> no rows selected > > in informix for the same tables > select * from aaa,outer bbb where a1=b1 and b2<> 'T';> > 1 abcd > 2 abcd 2 F It doesn't sound like you are wanting an outer join here. From what you describe (and the results from the oracle select above), it looks like you want an inner join. All the outer clause does is says if you don't find a corresponding record in the outer table, put nulls in the places where columns from that table should be. Try: select * from aaa, bbb where a1=b1 and b2 != 'T'; > > select * from aaa,outer bbb where a1=b1 and b2 <> 'G';> > 1 abcd 1 T > 2 abcd 2 F This isn't the same as the oracle statement above, were you aware of that? -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.