Tue, 20 Mar 2007 14:15:52 -0000 Ajay Arora wrote:
> [snip]
>And it gives me an error saying "e.extract_sequence" does not exist".
DROP TABLE IF EXISTS extract_master;
CREATE TABLE extract_master (
extract_sequence INTEGER PRIMARY KEY,
file_location TEXT,
active integer
);
INSERT INTO extract_master VALUES (1,'file1',1);
INSERT INTO extract_master VALUES (2,'file1',0);
DROP TABLE IF EXISTS extract_parameter;
CREATE TABLE extract_parameter (
extract_sequence integer,
start_date text,
end_date text,
portfolio_code integer
);
INSERT INTO extract_parameter VALUES
(1,'2007-01-01','2007-01-02',1);
INSERT INTO extract_parameter VALUES
(1,'2007-02-01','2007-02-02',1);
INSERT INTO extract_parameter VALUES
(2,'2007-03-01','2007-03-02',2);
INSERT INTO extract_parameter VALUES
(2,'2007-04-01','2007-04-02',2);
DROP TABLE IF EXISTS extract_status;
CREATE TABLE extract_status (
extract_sequence integer,
status integer,
datetime text
);
INSERT INTO extract_status VALUES (1,1,'2007-01-15 12:00:00');
INSERT INTO extract_status VALUES (2,2,'2007-03-15 12:00:00');
select -- [1]
a.extract_sequence,a.file_location,a.active,
b.start_date,b.end_date,b.portfolio_code,
c.status
from extract_master AS a,
extract_parameter AS b,
(select -- [2]
extract_sequence,status
from extract_status AS e
where datetime = (select -- [3]
max(datetime)
from extract_status AS s
where e.extract_sequence =
s.extract_sequence
)
) AS c
where a.extract_sequence = b.extract_sequence
and b.extract_sequence = c.extract_sequence
and c.extract_sequence = a.extract_sequence;
results in:
1|file1|1|2007-01-01|2007-01-02|1|1
1|file1|1|2007-02-01|2007-02-02|1|1
2|file1|0|2007-03-01|2007-03-02|2|2
2|file1|0|2007-04-01|2007-04-02|2|2
I don't know if it makes sense, but in sqlite-3.3.13 I didn't
get the error you mentioned.
Just guessing: In earlier version of sqlite the alias e can't be
used in the subselect, in other words: Support for correlated
subqueries was added in v3.1.0.
--
( Kees Nuyt
)
c[_]
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
