I don't claim to be a master of SQL, but isn't "select b + 1 as c" its
own query, without access to the "2 as b" in the outer query?
On 4/2/06, Alexander Kozlovsky <[EMAIL PROTECTED]> wrote:
> Hello!
> Can anybody tell me, what is wrong with this simple query:
>
> select a, (select c
> from (select b + 1 as c) as Table2) as d
> from (select 1 as a, 2 as b) as Table1
>
> The error arise at line 2 ("no such column: b").
> AFAIK, the syntax is perfectly correct.
>
> This error arise when query contains double-nested subquery
> in its SELECT clause, and the inner subqery refers on columns
> of the main query.
>
> I think, this is bug in SQLite, but I'm not entirely sure,
> because similar error arise in MS SQL Server 2000.
>
> Any thought?
>
> -----------------------------------------------------------
>
> Below is (almost) real-life example of query with double-nested
> subquery in SELECT clause, which refers on columns of the main
> query. Consider this tables:
>
>
> create table Products
> (
> name text primary key
> );
>
> create table Orders
> (
> order_date timestamp not null,
> order_id integer not null,
>
> -- orders with different dates can have same id
> primary key (order_date, order_id)
> );
>
> create table OrderDetails
> (
> order_date timestamp,
> order_id integer,
> product_name text,
> amount integer not null,
>
> primary key (order_date, order_id, product_name),
> foreign key (order_date, order_id)
> references Orders (order_date, order_id),
> foreign key (product_name)
> references Products (name)
> );
>
>
> The question is: "for each pair of products calculate number of orders
> where this two products has been shipped together" (I'm sorry if my
> English is not correct). One of possible solutions is:
>
>
> select X, Y, (select count(*)
> from (select order_date, order_id
> from OrderDetails where product_name = X
> intersect
> select order_date, order_id
> from OrderDetails where product_name = Y) as Table2
> ) as Z
> from (select P1.name as X, P1.name as Y
> from Products P1, Products P2
> where P1.name < P2.name) as Table1
>
>
> This query does not work, because SQLite complaints on comparisons
> "product_name = X" and "product_name = Y".
>
> I solve this problem by rewriting of the original query as:
>
> select D1.product_name, D2.product_name, count(*)
> from OrderDetails D1 inner join OrderDetails D2
> using (order_date, order_id)
> where D1.product_name < D2.product_name
> group by D1.product_name, D2.product_name
>
> But (IMHO) original query is also correct. Is I'm mistaken?
>
>
> --
> Best regards,
> Alexander mailto:[EMAIL PROTECTED]
>
>
--
Cory Nelson
http://www.int64.org
