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]
