DATA "the_geom from (SELECT sub1.* FROM ( SELECT r.the_geom, r.name, COALESCE(SUM(d.value), -9999) AS value, r.id AS uid FROM cereals_yield AS d RIGHT JOIN countries_view AS c ON c.id = d.id_country RIGHT JOIN subregions AS r ON r.id = c.sreg_id WHERE d.year_start = 2006 GROUP BY r.name, r.the_geom, r.id ) AS sub1 LEFT JOIN ( SELECT * FROM crosstab( 'SELECT r.name AS name, d.year_start AS year, SUM(d.value * d_nom.value) / SUM(d_nom.value) AS value FROM in_cereals_yield AS d RIGHT JOIN countries_view AS c ON c.id = d.id_country RIGHT JOIN rice_harvested AS d_nom ON d_nom.id_country = d.id_country RIGHT JOIN subregions AS r ON r.id = c.sreg_id WHERE (d.year_start = 2003 ) AND (d_nom.year_start = 2003 ) AND d_nom.value <> 0 GROUP BY r.name, d.year_start ORDER BY 1,2;', 3) AS ct(name varchar, y_2003 numeric) ORDER BY 2 NULLS LAST ) AS sub2 ON sub2.name = sub1.name) AS foo USING UNIQUE uid USING SRID= 4326
On Fri, Mar 20, 2009 at 5:44 AM, Stefan Schwarzer <[email protected]> wrote: > Hi there, > > I have a unfortunately rather complex query for my postgis layers. > > But if I take the SQL and insert it into PgAdmin, I get what I want: a table > with the_geom, name, value, uid. > > But when using this query in the map file, I suddenly get an error message: > > Warning: [MapServer Error]: prepare_database(): Error declaring cursor: > ERROR: syntax error at end of input LINE 2: ...D(-180.200445434298 > -90,180.200445434298 90)'::BOX3D, 4326 ) ^ With query string: DECLARE > mycursor BINARY CURSOR FOR SELECT > "value"::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),uid::text > from ( SELECT * FROM (SELECT sub1.* FROM ( SELECT r.the_geom, r.name, > COALESCE(SUM(d.value), -9999) AS value, r.id AS uid FROM cereals_yield AS d > RIGHT JOIN countries_view AS c ON c.id = d.id_country RIGHT JOIN subregions > AS r ON r.id = c.sreg_id WHERE d.year_start = 2006 GROUP BY r.name, > r.the_geom, r.id ) AS sub1 LEFT JOIN ( SELECT * FROM crosstab( 'SELECT > r.name AS name, d.year_start AS year, SUM(d.value * d_nom.value) / > SUM(d_nom.value) AS value FROM in_cereals_yield AS d RIGHT JOIN > countries_view AS c ON c.id = d.id_country RIGHT JOIN rice_harvested AS > d_nom ON d_nom.id_country = d.id_country RIGHT JOIN subregions AS r ON r. in > /www/geodataportal/htdocs/mod_map/map.php on line 1331 > > > Here is the query: > > SELECT * FROM (SELECT sub1.* FROM ( SELECT r.the_geom, r.name, > COALESCE(SUM(d.value), -9999) AS value, r.id AS uid FROM cereals_yield AS d > RIGHT JOIN countries_view AS c ON c.id = d.id_country RIGHT JOIN subregions > AS r ON r.id = c.sreg_id WHERE d.year_start = 2006 GROUP BY r.name, > r.the_geom, r.id ) AS sub1 LEFT JOIN ( SELECT * FROM crosstab( 'SELECT > r.name AS name, d.year_start AS year, SUM(d.value * d_nom.value) / > SUM(d_nom.value) AS value FROM in_cereals_yield AS d RIGHT JOIN > countries_view AS c ON c.id = d.id_country RIGHT JOIN rice_harvested AS > d_nom ON d_nom.id_country = d.id_country RIGHT JOIN subregions AS r ON r.id > = c.sreg_id WHERE (d.year_start = 2003 ) AND (d_nom.year_start = 2003 ) AND > d_nom.value <> 0 GROUP BY r.name, d.year_start ORDER BY 1,2;', 3) AS ct(name > varchar, y_2003 numeric) ORDER BY 2 NULLS LAST ) AS sub2 ON sub2.name = > sub1.name) AS foo > > > And within the mapfile I attach a "USING UNIQUE uid USING srid=4326" to the > query. > > > Can anyone give me a hint what the problem might be? > > Thanks for any adive! > > Stef > _______________________________________________ > mapserver-users mailing list > [email protected] > http://lists.osgeo.org/mailman/listinfo/mapserver-users > _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
