Re: select from (select)
A user wanted Oracle-style "SELECT ... FROM (SELECT ...)" (derived tables), which Informix rejects. The answer given was to wrap the inner query as a collection-derived table: SELECT ... FROM TABLE(MULTISET(SELECT ...)) alias, using AS to name columns; multiple MULTISET blocks can be used in one query and joined like ordinary tables, though a plain join is usually simpler and faster. The poster's follow-up query returned duplicated rows (effectively a cartesian product from unjoined MULTISET blocks), and the thread ends with a snippy remark about free support rather than a fix for that issue.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
> Thanks a lot .Query is working fine . Could you let me know on how to
> pass multiple columns in select clause which are present in different
> tables.
You can use the "AS" clause to name fields(as always), both SELECT
statements are full-fedeged statements.
> > > I am trying to run an sql statement which is like select from (select)
> > > statement.This sql query works fine in oracle database & throws an error
> > > in informix.
> > Yes - a reasonably well-known problem.
> > Is there any alternative
> > > way i which this sql statement can be run.Any help is highly
> > > appretiated.
> > Yes, but it isn't pretty.
> > SELECT ... FROM TABLE(MULTISET(SELECT What, You, Wanted FROM WhereEver)),
Hi Adam,
Could you give me some example on the same . I tried using the as
clause too but getting error. FYI , i need to display data from various
columns which are from different tables.
Thanks for all your help.
Rakesh.
Adam Tauno Williams wrote:
> > Thanks a lot .Query is working fine . Could you let me know on how to
> > pass multiple columns in select clause which are present in different
> > tables.
>
> You can use the "AS" clause to name fields(as always), both SELECT
> statements are full-fedeged statements.
>
> > > > I am trying to run an sql statement which is like select from (select)
> > > > statement.This sql query works fine in oracle database & throws an error
> > > > in informix.
> > > Yes - a reasonably well-known problem.
> > > Is there any alternative
> > > > way i which this sql statement can be run.Any help is highly
> > > > appretiated.
> > > Yes, but it isn't pretty.
> > > SELECT ... FROM TABLE(MULTISET(SELECT What, You, Wanted FROM WhereEver)),
Adam,
Also do let me know the Syntax of MULTISET . Can i be able to use
multiple MULTISET in a
single query.
Thanks
Rakesh.
rakesh_sa@yahoo.com wrote:
> Hi Adam,
>
> Could you give me some example on the same . I tried using the as
> clause too but getting error. FYI , i need to display data from various
> columns which are from different tables.
>
> Thanks for all your help.
>
> Rakesh.
>
> Adam Tauno Williams wrote:
> > > Thanks a lot .Query is working fine . Could you let me know on how to
> > > pass multiple columns in select clause which are present in different
> > > tables.
> >
> > You can use the "AS" clause to name fields(as always), both SELECT
> > statements are full-fedeged statements.
> >
> > > > > I am trying to run an sql statement which is like select from (select)
> > > > > statement.This sql query works fine in oracle database & throws an error
> > > > > in informix.
> > > > Yes - a reasonably well-known problem.
> > > > Is there any alternative
> > > > > way i which this sql statement can be run.Any help is highly
> > > > > appretiated.
> > > > Yes, but it isn't pretty.
> > > > SELECT ... FROM TABLE(MULTISET(SELECT What, You, Wanted FROM WhereEver)),
> Could you give me some example on the same . I tried using the as
> clause too but getting error. FYI , i need to display data from various
> columns which are from different tables.
Perhaps you aren't explaining yourself well. What does it matter that
the data is from seperate tables? You just do a join.
SELECT *
FROM TABLE
(MULTISET(
SELECT equipment_id::INT AS equipment_id,
oe_oem_desc AS oem_description
FROM equipment_master em, oemr om
WHERE em.oem_code = om.oe_oem_code)
)
> Adam Tauno Williams wrote:
> > > Thanks a lot .Query is working fine . Could you let me know on how to
> > > pass multiple columns in select clause which are present in different
> > > tables.
> > You can use the "AS" clause to name fields(as always), both SELECT
> > statements are full-fedeged statements.
> > > > > I am trying to run an sql statement which is like select from (select)
> > > > > statement.This sql query works fine in oracle database & throws an error
> > > > > in informix.
> > > > Yes - a reasonably well-known problem.
> > > > Is there any alternative
> > > > > way i which this sql statement can be run.Any help is highly
> > > > > appretiated.
> > > > Yes, but it isn't pretty.
> > > > SELECT ... FROM TABLE(MULTISET(SELECT What, You, Wanted FROM WhereEver)),>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
> Also do let me know the Syntax of MULTISET . Can i be able to use
> multiple MULTISET in a
> single query.
Sure, they are just virtual tables, they work just like tables.
SELECT a.equipment_id, oemr.oe_oem_desc
FROM TABLE
(MULTISET(
SELECT equipment_id::INT AS equipment_id, oem_code
FROM equipment_master em)
) a,
TABLE(MULTISET(SELECT ve_oem_code AS oem_code FROM vendorr)) b,
oemr
WHERE a.oem_code = b.oem_code
AND a.oem_code = oemr.oe_oem_code
Of course, in most cases you could do this much more efficiently with a
normal join.
Please help me out in understanding what exactly does MULTISET do in
the queries ? Output of the below mentioned query should be 1 row but
the same data is repeated twice ?
Here is my query which i created based on your example.
select *
from TABLE (MULTISET(select p.first_name as BATSMAN ,bout.batsman_out_ref_id
from person p, batsman_out bout , match m ,reference_item r ,
team_details td , batsman bat
where m.match_id = 230 and bat.batsman_id = bout.batsman_id and
bat.team_details_id = td.team_details_id and
td.person_id = p.person_id)) ,
TABLE (MULTISET(select b.runs as RUNS, b.fours as FOURS , b.sixes as
SIXES from batsman b)) ,
TABLE (MULTISET(select p.first_name as BOWLER from person p,
batsman_out bout , match m ,reference_item r , team_details td ,
bowler bow where m.match_id = 230 and bow.bowler_id = bout.bowler_id
and bow.team_details_id = td.team_details_id and
td.person_id = p.person_id))
group by 1,2,3,4,5,6
Thanks a lot . Appretiate your help.
Regards
Rakesh.
Adam Tauno Williams wrote:
> > Could you give me some example on the same . I tried using the as
> > clause too but getting error. FYI , i need to display data from various
> > columns which are from different tables.
>
> Perhaps you aren't explaining yourself well. What does it matter that
> the data is from seperate tables? You just do a join.
>
> SELECT *
> FROM TABLE
> (MULTISET(
> SELECT equipment_id::INT AS equipment_id,
> oe_oem_desc AS oem_description
> FROM equipment_master em, oemr om
> WHERE em.oem_code = om.oe_oem_code)
> )>
> > Adam Tauno Williams wrote:
> > > > Thanks a lot .Query is working fine . Could you let me know on how to
> > > > pass multiple columns in select clause which are present in different
> > > > tables.
> > > You can use the "AS" clause to name fields(as always), both SELECT
> > > statements are full-fedeged statements.
> > > > > > I am trying to run an sql statement which is like select from (select)
> > > > > > statement.This sql query works fine in oracle database & throws an error
> > > > > > in informix.
> > > > > Yes - a reasonably well-known problem.
> > > > > Is there any alternative
> > > > > > way i which this sql statement can be run.Any help is highly
> > > > > > appretiated.
> > > > > Yes, but it isn't pretty.
> > > > > SELECT ... FROM TABLE(MULTISET(SELECT What, You, Wanted FROM WhereEver)),> >
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org
> > http://www.iiug.org/mailman/listinfo/informix-list
Adam,
Awaiting for your reply . Did you get time to see to the query which i
had sent.
Data is repeating twice which i am unable to get at all . Help me out
???
Thanks
Rakesh.
Adam Tauno Williams wrote:
> > Also do let me know the Syntax of MULTISET . Can i be able to use
> > multiple MULTISET in a
> > single query.
>
> Sure, they are just virtual tables, they work just like tables.
>
> SELECT a.equipment_id, oemr.oe_oem_desc
> FROM TABLE
> (MULTISET(
> SELECT equipment_id::INT AS equipment_id, oem_code
> FROM equipment_master em)
> ) a,
> TABLE(MULTISET(SELECT ve_oem_code AS oem_code FROM vendorr)) b,
> oemr
> WHERE a.oem_code = b.oem_code
> AND a.oem_code = oemr.oe_oem_code>
> Of course, in most cases you could do this much more efficiently with a
> normal join.
rakesh_sa@yahoo.com wrote: > Adam, > > Awaiting for your reply . Did you get time to see to the query which i > had sent. > Data is repeating twice which i am unable to get at all . Help me out > ??? > What do you think this is - a PAID FOR support forum? - sheesh!
I am really sorry !!! . My intention was not to hurt you all . Clive Eisen wrote: > rakesh_sa@yahoo.com wrote: > > Adam, > > > > Awaiting for your reply . Did you get time to see to the query which i > > had sent. > > Data is repeating twice which i am unable to get at all . Help me out > > ??? > > > > What do you think this is - a PAID FOR support forum? - sheesh!