Re: Weird SQL


"Michel Cadot" <micadot{at}altern{dot}org> wrote in message
news:446cc92f$0$10184$636a55ce@xxxxxxxxxxxxxxx

Sorry it was the worst example i ever posted.
What i meant was you can:

SQL> select (select c2 from t2 where c2=c1) from t1;

no rows selected

But you can't:

SQL> select (select c2 from (select c2 from t2 where c2=c1)) from t1;
select (select c2 from (select c2 from t2 where c2=c1)) from t1
*
ERROR at line 1:
ORA-00904: "C1": invalid identifier


Well, when i say you can't it's no more true.
I am just checking in 10gR2 and now it works (it does not in 9iR2 and
before):

SQL> select (select c2 from (select c2 from t2 where c2=c1)) from t1;

no rows selected

SQL> select (select c2 from (select c2 from (select c2 from t2 where
c2=c1) where c2=c1)) from t1;

no rows selected


Hmm, doesn't work for me: 10.2.0.2 says

ORA-00904: "C1": invalid identifier

Tried a few other forms and still got ORA-904 if C1 is
referenced below the first level in an inline view.
And here's an excerpt from 10.2 docs (emphasis mine):

[quote]
A subquery in the *FROM clause* of a SELECT statement is also
called an *inline view*. A subquery in the *WHERE clause* of a
SELECT statement is also called a *nested subquery*.
[/quote]

Note that only a subquery in WHERE clause can be
called nested.

[quote]
Oracle performs a "correlated subquery" when a *nested
subquery* references a column from a table referred to a
parent statement *any number of levels* above the subquery.
[/quote]

So this sentence states that a subquery in WHERE clause
can see columns of any table referred to any of its parent
statements. However, this is not true for inline views and
(not completely sure, but looks like) scalar subqueries.

--
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com


.

Relevant Pages

  • Re: Complex Subquery: ...FROM(TRANSFORM... systax - (fixed linebreaks)
    ... > Is it possible to in Access SQL or SQLServer SQL to have a Compound SQL ... > subquery, but maybe there is a simple syntax error. ... I need the PK's in the cross tab query to ... > This WHERE has the same IN Clause as J1T4. ...
    (microsoft.public.access.queries)
  • Re: query wont run in access 97
    ... It returns a syntax error FROM Clause. ... A Jet subquery used in a FROM clause will always ... it is wrapped in brackets with an ending period ... or the Jet query parser will choke on them. ...
    (microsoft.public.access.queries)
  • Re: Help with derived table SQL statement in Access
    ... SELECT expression on the FROM clause, but that is just that, a table ... You can use three forms of syntax to create a subquery: ... You can also use table name aliases in a subquery to refer to tables ... than the average salary of all employees having the same job title. ...
    (microsoft.public.access.queries)
  • Re: Insert via another tables columns
    ... The UPDATE with a SET (SELECT subquery) also requires that col_a1 and col_a2 ... UPDATE clause, beside the subquery in the SET clause. ... Earlier version of FoxPro need to use xbase REPLACE command or a mix of SQL ... I try the same in SQL Server and works great. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: subquery
    ... Generatrice ON [transport de generatrice].Nom = Generatrice.Nom) INNER ... SELECT clause, ... You can "correlate" the subquery back to the main ... on a field in the main query. ...
    (microsoft.public.access.queries)
Loading