Re: select from (select)

rakesh_sa@yahoo.com wrote: > Hi Prateek, > > Please see to the query mentioned below.This query works fine in oracle > & not in informix. > Any hints highly appretiated. > > select a.col1 , b.col2 from > (select column1 col1 from table1) a, > (select column2 col2 from table2) b, > where a.col1 = b.col2> > Thanks > Rakesh. > > <SNIP> Prateek: In the general case Jonathan's proposal of using the 9.xx+ syntax: TABLE(MULTISET(SELECT...)) is the equivalent of the syntax above, however, ALL of the 'FROM (select..)' constructs can be produced using either temp tables or views and most can be implemented as simple joins. Also the TABLE and MULTISET casts are not available in earlier releases of IDS. So here are the options as I see them: Using temp tables: select column1 col1 from table1 into temp a; select column2 col2 from table2 into temp b; select a.col1, b.col2 from a, b where a.col1 = b.col2; drop table a; drop table b; Another solution using views: create view first(col1) as select column1 col1 from table1; create view second(col2) as select column2 col2 from table2; select a.col1, b.col2 from first a, second b where a.col1 = b.col2; drop view first; drop view second; -- OR a bit less dramatically using a simple join -- select a.column1 as col1, b.column2 as col2 from table1 a, table2 b where col1 = col2; -- OR using ANSI syntax -- select a.column1 as col1, b.column2 as col2 from table1 a join table2 b on col1 = col2; Art S. Kagel

Hi All, I am using IDS 9.4 . Using Multiset i have developed a query which is generating the desired output but takes around 3 minutes to generate a single record . I have updated the statistics but no improvement.Posting the query on the user group . Please suggest me on the same . Thanks !!! Regards Rakesh. select a.NAME , a.REF , a.RUNS , a.FOURS , a.SIXES from TABLE (MULTISET (select p.first_name NAME , bout.batsman_out_ref_id as REF, bout.batsman_id as OUTBATSMAN_ID , bat.batsman_id as BATSMAN_ID , td.team_details_id as TID, bat.team_details_id as BTID, td.person_id as TPID, p.person_id as PID, bat.runs as RUNS, bat.FOURS as FOURS, bat.SIXES as SIXES from person p, batsman_out bout , match m , reference_item r ,team_details td , batsman bat )) a where a.BATSMAN_ID = a.OUTBATSMAN_ID and a.BTID = a.TID and a.TPID = a.PID group by 1,2,3,4,5 Art S. Kagel wrote: > rakesh_sa@yahoo.com wrote: > > Hi Prateek, > > > > Please see to the query mentioned below.This query works fine in oracle > > & not in informix. > > Any hints highly appretiated. > > > > select a.col1 , b.col2 from > > (select column1 col1 from table1) a, > > (select column2 col2 from table2) b, > > where a.col1 = b.col2> > > > Thanks > > Rakesh. > > > > <SNIP> > > Prateek: > > In the general case Jonathan's proposal of using the 9.xx+ syntax: > TABLE(MULTISET(SELECT...)) is the equivalent of the syntax above, however, > ALL of the 'FROM (select..)' constructs can be produced using either temp > tables or views and most can be implemented as simple joins. Also the TABLE > and MULTISET casts are not available in earlier releases of IDS. So here > are the options as I see them: > > Using temp tables: > > select column1 col1 from table1 into temp a; > select column2 col2 from table2 into temp b; > > select a.col1, b.col2 > from a, b > where a.col1 = b.col2;> > drop table a; > drop table b;> > Another solution using views: > > create view first(col1) as select column1 col1 from table1; > create view second(col2) as select column2 col2 from table2;> > select a.col1, b.col2 > from first a, second b > where a.col1 = b.col2;> > drop view first; > drop view second;> > -- OR a bit less dramatically using a simple join -- > > select a.column1 as col1, b.column2 as col2 > from table1 a, table2 b > where col1 = col2;> > -- OR using ANSI syntax -- > > select a.column1 as col1, b.column2 as col2 > from table1 a > join table2 b > on col1 = col2;> > Art S. Kagel

> I am using IDS 9.4 . Using Multiset i have developed a query which is > generating the desired output but takes around 3 minutes to generate a > single record . I have updated the statistics but no > improvement.Posting the query on the user group . Please suggest me on > the same . Thanks !!! > select a.NAME , a.REF , a.RUNS , a.FOURS , a.SIXES > from TABLE (MULTISET > (select p.first_name NAME , > bout.batsman_out_ref_id as REF, > bout.batsman_id as OUTBATSMAN_ID , > bat.batsman_id as BATSMAN_ID , > td.team_details_id as TID, > bat.team_details_id as BTID, > td.person_id as TPID, > p.person_id as PID, > bat.runs as RUNS, > bat.FOURS as FOURS, > bat.SIXES as SIXES > from person p, batsman_out bout , match m , reference_item r ,> team_details td , batsman bat )) a > where > a.BATSMAN_ID = a.OUTBATSMAN_ID and a.BTID = a.TID and a.TPID = a.PID > group by 1,2,3,4,5 I don't see any point to using a virtual table for this query. Your inner query is probably generating a Cartesian product, so of course it takes forever - you have multiple tables with NO JOIN CRITERIA! There is no reason that I can see why you couldn't accomplish the above via a 'normal' query with joins.

I agree with Adam. Could you simplify your query by using something like the following (which does not have all the join critiria entered as I didn't feel like guessing your structure - and I typed in here so likely a spelling mistake, extra comma etc here and there). I suspect you'll probably want an outer on a few of these tables so you can see the statistics for individuals with no at bats SELECT person.first_name, batsmanout.batsman_out_ref_id, sum(batsman.runs) sum_runs, sum(batsman.fours) sum_fours, sum(batsman.sixes) sum_sixes FROM person, batsman, team_details, batsman_out, match, reference_item WHERE person.person_id = batsman.batsman_id AND batsman.team_details_id = team_details.team_details_id -- your additional joins here for batsman_out, match, reference_item GROUP BY first_name, batsman_out_ref_id Adam Tauno Williams wrote: > > I am using IDS 9.4 . Using Multiset i have developed a query which is > > generating the desired output but takes around 3 minutes to generate a > > single record . I have updated the statistics but no > > improvement.Posting the query on the user group . Please suggest me on > > the same . Thanks !!! > > select a.NAME , a.REF , a.RUNS , a.FOURS , a.SIXES > > from TABLE (MULTISET > > (select p.first_name NAME , > > bout.batsman_out_ref_id as REF, > > bout.batsman_id as OUTBATSMAN_ID , > > bat.batsman_id as BATSMAN_ID , > > td.team_details_id as TID, > > bat.team_details_id as BTID, > > td.person_id as TPID, > > p.person_id as PID, > > bat.runs as RUNS, > > bat.FOURS as FOURS, > > bat.SIXES as SIXES > > from person p, batsman_out bout , match m , reference_item r ,> > team_details td , batsman bat )) a > > where > > a.BATSMAN_ID = a.OUTBATSMAN_ID and a.BTID = a.TID and a.TPID = a.PID > > group by 1,2,3,4,5 > > I don't see any point to using a virtual table for this query. Your > inner query is probably generating a Cartesian product, so of course it > takes forever - you have multiple tables with NO JOIN CRITERIA! > > There is no reason that I can see why you couldn't accomplish the above > via a 'normal' query with joins.