[mapserver-users] Strange Postgis/Mapserver SQL behaviour

[Stefan Schwarzer]

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