[MonetDB-users] Temporal coalescing query error

Alexander S shkapsky at cs.ucla.edu
Sat Oct 29 04:24:03 CEST 2011


Hello,

I'm attempting to use monetdb for some temporal queries.  Some queries
require the output to be coalesced.  For example, with schema emp(empno int,
ts date, te date) the tuples (00001, '1/1/2001', '2/1/2001'), (00001,
'2/1/2001', '3/1/2001') are output as (00001, '1/1/2001', '3/1/2001').  

My coalescing query works in both MS SQL Server and DB2 but MonetDB gives me
the error "SELECT: no such column 'm.ts'".  1) A leaner alternative query
form using window clauses (ROWS UNBOUNDED PRECEDING) doesn't appear to be
supported in MonetDB. So I'm stuck.  Any help would be appreciated.

Query:
WITH Emps(empno, ts, te) AS
(SELECT empno, ts, te FROM emp)
SELECT DISTINCT F.empno, F.ts, L.te
FROM Emps AS F, Emps AS L
WHERE F.ts < L.te
AND F.empno = L.empno
AND NOT EXISTS
	(SELECT * FROM Emps AS M
		WHERE M.empno = F.empno
		AND F.ts < M.ts AND M.ts < L.te	
		AND NOT EXISTS
		(SELECT * FROM Emps AS T1
			WHERE T1.empno = F.empno
			AND T1.ts < M.ts AND M.ts <= T1.te)
	)
AND NOT EXISTS
	(SELECT * FROM Emps AS T2
		WHERE T2.empno = F.empno
		AND	((T2.ts < F.ts AND F.ts <= T2.te)
			OR	(T2.ts <= L.te AND L.te < T2.te))
	);

Thanks,
Alex
-- 
View this message in context: http://old.nabble.com/Temporal-coalescing-query-error-tp32742108p32742108.html
Sent from the monetdb-users mailing list archive at Nabble.com.





More information about the users-list mailing list