"derived" tables (multiset) – Informix Archive

Tom Lehr — Thu, 18 Mar 2010 12:52:52 -0700 (PDT) — source: Usenet: comp.databases.informix

Is there anything inherently bad about using derived tables in Informix world? select activity.id, t.latest_event_id from activity left join table(multiset( select activity_id, latest_event_id from appt_activity where latest_event_id = 57799475)) t on activity.id = t.activity_id where activity.id between 4256526 and 4256599

↪ replying to Tom Lehr

Art Kagel — Thu, 18 Mar 2010 18:02:12 -0400 — source: Usenet: comp.databases.informix

No, and BTW if you are running 11.xx you don't have to use the TABLE(MULTISET()) syntax any longer: select activity.id, t.latest_event_id from activity left join ( select activity_id, latest_event_id from appt_activity where latest_event_id = 57799475 ) as t on activity.id = t.activity_id where activity.id between 4256526 and 4256599; There's nothing wrong with using derived tables, or table expressions, in the FROM clause in Informix, it's just that we didn't have the syntax available until recent releases and even in 9.xx the TABLE(MULTISET( )) syntax was so cumbersome that many avoided using it. In addition, since Informix has always had real session specific temp tables that were indexable, using temp tables instead of derived tables for this kind of query made much more sense than it does in say Oracle which still does not have real session-private temp tables. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 18, 2010 at 3:52 PM, Tom Lehr <tomcaml@gmail.com> wrote: > Is there anything inherently bad about using derived tables in > Informix world? > > select > activity.id, > t.latest_event_id > from > activity left join > table(multiset( > select > activity_id, > latest_event_id > from > appt_activity > where > latest_event_id = 57799475)) t > on activity.id = t.activity_id > where > activity.id between 4256526 and 4256599 > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

↪ replying to Tom Lehr

Fernando Nunes — Thu, 18 Mar 2010 22:53:30 +0000 — source: Usenet: comp.databases.informix

The only thing inherently bad in using hat syntax is that it probably means you're in version 10 or less.... :) As ART wrote, in version 11 you can use the "normal" syntax. Temp tables can be more efficient because you can index them etc. But in some scenarios they can't be used (some tools only allow us to specify one query...) On Thu, Mar 18, 2010 at 7:52 PM, Tom Lehr <tomcaml@gmail.com> wrote: > Is there anything inherently bad about using derived tables in > Informix world? > > select > activity.id, > t.latest_event_id > from > activity left join > table(multiset( > select > activity_id, > latest_event_id > from > appt_activity > where > latest_event_id = 57799475)) t > on activity.id = t.activity_id > where > activity.id between 4256526 and 4256599 > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...