joining to a result set

Posted in 2006

frederickflint@hotmail.com — 10 Feb 2006 13:57:51 -0800 — source: Usenet: comp.databases.informix

In ansi SQL (at least, I think it's ANSI) I can write something like: Select tbla.ColumnA, tbla.ColumnB from TableA tbla outer join (select tblb.ColumnA, tblb.ColumnB from TableB tblb ) as rslta on rslta.ColumnA = tbla.ColumnA I can get this syntax to work on SQL Server, Oracle and DB2. Informix barfs. Do I need to change this syntax? Do it a completely different way? Or am i just out of luck? Andrew :)

↪ replying to frederickflint@hotmail.com

Serge Rielau — Fri, 10 Feb 2006 17:01:05 -0500 — source: Usenet: comp.databases.informix

frederickflint@hotmail.com wrote: > In ansi SQL (at least, I think it's ANSI) I can write something like: > > Select tbla.ColumnA, > tbla.ColumnB > from TableA tbla > outer join (select tblb.ColumnA, > tblb.ColumnB > from TableB tblb > ) as rslta > on rslta.ColumnA = tbla.ColumnA> > I can get this syntax to work on SQL Server, Oracle and DB2. > Informix barfs. > > Do I need to change this syntax? Do it a completely different way? Or > am i just out of luck? > > Andrew :) > I assume your real example is more complex (there is no need for the nested select)? Try: In ansi SQL (at least, I think it's ANSI) I can write something like: Select tbla.ColumnA, tbla.ColumnB from TableA tbla outer join TABLE(MULTISET(select tblb.ColumnA, tblb.ColumnB from TableB tblb )) as rslta(ColumnA, ColumnB) on rslta.ColumnA = tbla.ColumnA Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab