select from (select)
A user asked why a query using derived tables (SELECT ... FROM (SELECT ...) a, (SELECT ...) b WHERE ...), which works in Oracle, fails in Informix. After another poster asked to see the actual SQL, Jonathan Leffler confirmed this is a known Informix limitation and gave the workaround: wrap each inline view as SELECT ... FROM TABLE(MULTISET(SELECT cols FROM table)), ... The original poster reported the query then worked, and followed up asking how to select multiple columns from different tables; no answer to that follow-up is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, 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.Is there any alternative way i which this sql statement can be run.Any help is highly appretiated. Thanks in Advance. Regards Rakesh.
I tried a normal select query
select col_A, (select distinct 1 from Tab_A) from Tab_A
its working fine on Informix,what sort of query u have can u post it on the group..
On 16 Jul 2006 21:58:36 -0700, rakesh_sa@yahoo.com <rakesh_sa@yahoo.com> wrote:
> Hi All,
>
> 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.Is
> there any alternative
> way i which this sql statement can be run.Any help is highly
> appretiated.
>
> Thanks in Advance.
>
> Regards
> Rakesh.
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
--
Regards,
Prateek Jain
I tried a normal select query and its working fine on informix
PFB the query
select Col_A, (select distinct 1 from TAB_A) from TAB_A
Can u post ur query ???.
On 16 Jul 2006 21:58:36 -0700, rakesh_sa@yahoo.com <rakesh_sa@yahoo.com> wrote:
> Hi All,
>
> 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.Is
> there any alternative
> way i which this sql statement can be run.Any help is highly
> appretiated.
>
> Thanks in Advance.
>
> Regards
> Rakesh.
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
--
Regards,
Prateek Jain
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.
Prateek Jain wrote:
> I tried a normal select query
> select col_A, (select distinct 1 from Tab_A) from Tab_A
> its working fine on Informix,> what sort of query u have can u post it on the group..
>
>
>
> On 16 Jul 2006 21:58:36 -0700, rakesh_sa@yahoo.com <rakesh_sa@yahoo.com> wrote:
> > Hi All,
> >
> > 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.Is
> > there any alternative
> > way i which this sql statement can be run.Any help is highly
> > appretiated.
> >
> > Thanks in Advance.
> >
> > Regards
> > Rakesh.
> >
> > _______________________________________________
> > Informix-list mailing list
> > Informix-list@iiug.org
> > http://www.iiug.org/mailman/listinfo/informix-list
> >
>
>
> --
> Regards,
> Prateek Jain
On 16 Jul 2006 21:58:36 -0700, rakesh_sa@yahoo.com <rakesh_sa@yahoo.com>
wrote:
> 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)),...
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Jonathan,
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.
Rakesh.
Jonathan Leffler wrote:
> On 16 Jul 2006 21:58:36 -0700, rakesh_sa@yahoo.com <rakesh_sa@yahoo.com>
> wrote:
>
> > 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)),> ...
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler@earthlink.net, jleffler@us.ibm.com
> Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
>
> ------=_Part_28452_31382959.1153120413327
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 1406
>
> <br><div><span class="gmail_quote">On 16 Jul 2006 21:58:36 -0700, <b class="gmail_sendername"><a href="mailto:rakesh_sa@yahoo.com">rakesh_sa@yahoo.com</a></b> <<a href="mailto:rakesh_sa@yahoo.com">rakesh_sa@yahoo.com</a>
> > wrote:</span><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I am trying to run an sql statement which is like select from (select)
> <br>statement.This sql query works fine in oracle database & throws an error in informix.</blockquote><div><br>Yes - a reasonably well-known problem. <br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
> Is there any alternative<br>way i which this sql statement can be run.Any help is highly<br>appretiated.<br></blockquote></div><br>Yes, but it isn't pretty.<br><br>SELECT ... FROM TABLE(MULTISET(SELECT What, You, Wanted FROM WhereEver)), ...
> <br clear="all"><br>-- <br>Jonathan Leffler #include <disclaimer.h><br>Email: <a href="mailto:jleffler@earthlink.net">jleffler@earthlink.net</a>, <a href="mailto:jleffler@us.ibm.com">jleffler@us.ibm.com
> </a><br>Guardian of DBD::Informix v2005.02 -- <a href="http://dbi.perl.org/">http://dbi.perl.org/</a>;
>
> ------=_Part_28452_31382959.1153120413327--