[Users] Subselect does not wor --- CONCLUSION

[Nicolai Tufar]

Many thanks to Friedrich, Roy and Dirk.

The conclusion is that constructs of the
type SELECT * FROM (SELECT....) are not possible
at the moment and are promised in next version.

Meanwhile views or temporary tables can be
used instead.

Thanks a lot!








On 1/13/06, Dirk Kraemer <kraemer at rz.uni-kiel.de> wrote:
> You could consider to create temporary views.
>
> Instead of
>
>   SELECT * FROM (SELECT * FROM X) AS YYY;
>
> you say
>
>   create view tt$sessionid as SELECT * FROM X;
>   SELECT * FROM tt$sessionid ;
>   drop view tt$sessionid;
>
> You could also use temporary tables. Then you
> would not have to care about different sessions
> and they would automatically be destroyed
> at session end.
>
> I agree that it would be better to have the
> feature. Chris Date wrote about this decades
> ago, it is the old orthogonality of language
> constraints: Why can I use a feature here and not
> there?
>
> Just a quick idea
>
> Regards
>
> Dirk
>
>
> Nicolai Tufar wrote:
> > Thank you your your help!
> >
> > On 1/13/06, Friedrich Pape <friedrich.pape at bsh.de> wrote:
> >
> >>Hi Nicolai,
> >>
> >>Ingres supports subselect but the sql syntax is diffent.
> >>here two short examples from sql reference manual Ingres R3
> >>
> >>Subselects
> >>Subselects are select statements placed in a where or having clause;
> >
> >
> > So subselects are supported in WHERE and HAVING clause, no luck for
> > FROM clause. We are porting an existing application from MSSQL and
> > it relies hevily on subselects in FROM clause :(
> >
> > I downloaded Ingres source code and now am trying to fugure
> > out if Ingres' SQL parser has a clasue that followin FROM, there
> > can be only and only a table name.
> >
> > Thanks for your help Friedrich, I am still looking for a solution...
> >
> > the
> >
> >>results
> >>returned by the subselect are used to evaluate the conditions specified
> >>in the
> >>where or having clause. Subselects are also referred to as subqueries.
> >>Subselects must return a single column, and cannot include an order by
> >>or union
> >>clause.
> >>The following example uses a subselect to display all employees whose
> >>salary is
> >>above the average salary:
> >>select * from employees where salary >
> >>(select avg(salary) from employees);
> >>In the preceding example, the subselect returns a single value: the
> >>average salary.
> >>Subselects can also return sets of values. For example, the following query
> >>returns all employees in all departments managed by Barth.
> >>select ename from employees where edept in
> >>(select ddept from departments
> >>where dmgr = 'Barth');
> >>
> >>for more information look at
> >>http://opensource.ca.com/projects/ingres/documents
> >>
> >>
> >>Nicolai Tufar schrieb:
> >>
> >>
> >>>Greetings!
> >>>
> >>>Am I wrong or is it a real limitation of Ingres. Subselect does not work
> >>>when placed after FROM. Example:
> >>>
> >>>CREATE TABLE X (Y INTEGER);
> >>>SELECT * FROM (SELECT * FROM X) AS YYY;
> >>>  E_US0845 Table 'select' does not exist or is not owned by you.
> >>>
> >>>Every other database, even MySQL supports it. Is it true that Ingres does not
> >>>support it?
> >>>
> >>>Kindest regards,
> >>>Nicolai Tufar
> >>>_______________________________________________
> >>>Users mailing list
> >>>Users at ingres.ca.com
> >>>http://ingres.ca.com/mailman/listinfo/users
> >>
> >>--
> >>
> >>MfG, Friedrich Pape
> >>---------------------------------------------------------
> >>  Bundesamt fuer Seeschifffahrt und Hydrographie  (BSH)
> >>  Friedrich Pape (Z4305)
> >>  Bernhard-Nocht-Str. 78            Tel: +49 (0) 40 3190-2435
> >>  20359 Hamburg             Fax: +49 (0) 40 3190-5000
> >>  http://www.bsh.de/            email: friedrich.pape at bsh.de
> >>---------------------------------------------------------
> >>
> >>_______________________________________________
> >>Users mailing list
> >>Users at ingres.ca.com
> >>http://ingres.ca.com/mailman/listinfo/users
> >>
> >
> > _______________________________________________
> > Users mailing list
> > Users at ingres.ca.com
> > http://ingres.ca.com/mailman/listinfo/users
>
>