Apologies for the misposting, a result of typos and several versions of these queries that I was trying out. See below for more responses --
On 9/20/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
P Kishor wrote: > Gentlefolks,
..
> You have an error in the select statement inside your create view statement. sqlite> SELECT pt.id AS pt_id, Count(pt.id) AS pt_id_count, py.attr AS py_attr ...> FROM pt JOIN py ON ...> ( ...> (pt.x BETWEEN py.xmin AND py.xmax) AND ...> (pt.y BETWEEN py.ymin AND py.ymax) ...> ) ...> GROUP BY pt_id_count ...> HAVING pt_id_count = 1; SQL error: misuse of aggregate:
Yes, I got this error, and then corrected the query exactly as you have suggested. ..
The group by clause is wrong. You should change pt_count_id to pt_id. Furthermore, standard SQL (I'm not sure about sqlite here) doesn't allow you to use an alias name defined in the select clause in the having clause, because those output fields may not exist yet. Since the having clause will only return groups where the count is 1, there is no sense returning that constant value from the view. Try this instead. SELECT pt.id AS pt_id, py.attr AS py_attr FROM pt JOIN py ON ( (pt.x BETWEEN py.xmin AND py.xmax) AND (pt.y BETWEEN py.ymin AND py.ymax) ) GROUP BY pt.id HAVING count(pt.id) = 1;
Yes to this as well. I disovered that, and modified the HAVING to use the Count directly.
Also, our update statement will not work at all. Firstly, there is no AS clause allowed in an update statement. Secondly, there is no condition on the update so that all rows in the pt table will be changed to the same new value of attr if it executes. The value would be the first value returned by the subquery that is getting data from the view.
I was using Oracle-ish syntax which does allow an alias in an UPDATE statement. Nevertheless, I believe what Jay Sprenkle said holds a lot of water here. Because of Count and GROUP BY, the program has to compute the entire set before returning even a single result. I actually tried a CROSS JOIN like so SELECT py_attr FROM ( SELECT pt.id AS pt_id, pt.x AS pt_x, pt.y AS pt_y, py.xmin AS py_xmin, py.ymin AS py_ymin, py.xmax AS py_xmax, py.ymax AS py_ymax, py.attr AS py_attr FROM pt CROSS JOIN py ) WHERE pt_id = 1 AND ( (pt_x BETWEEN py_xmin AND py_xmax) AND (pt_y BETWEEN py_ymin AND py_ymax) ) LIMIT 5; and that returns values instantly. Take away the pt_id = 1 from the WHERE clause above, and it chugs along forever. It seems the only efficient way to do this is to not do it the database way -- that is, use a cursor. I prefer DBI, so I can fetchrow_arrayref, and loop through the results one by one. Takes a few hours, but actually does the job. I was hoping to do it as a set, but no cigar yet. Many thanks, -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation https://edu.osgeo.org/ ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
