[Mapserver-users] Problem with subquery

[henk]

Randy Page wrote:
> Hi All,
> 
>  
> 
> I am attempting to join an attribute table to a spatial table and am running
> into allot of errors.   Below is my basic setup:
> 
>  
> 
> -Both tables are stored in the same PostgreSQL database.   The spatial table
> is tracts_c, and the attribute table is c2000_01_tr.
> 
> -I am attempting to add a subquery to the data statement in the mapfile.   I
> can run the sql successfully in psql if I omit the "using unique oid using
> SRID=-1" portion of the statement.  If I include it, PSQL says there is a
> syntax error near "using".
> 
>  
> 
> Here is my data string:
> 
>  
> 
> DATA 'select the_geom FROM (SELECT tracts_c.the_geom AS the_geom,
> tracts_c.oid AS oid,"C2000_01_TR"."TOTPOP00" FROM tracts_c LEFT JOIN
> "C2000_01_TR" ON tracts_c.id = "C2000_01_TR"."NAME") AS foo using unique oid
> using SRID=-1'
> 
>  
> 
> I have tried ALOT of variations on this, but this is the only format that
> will run in PSQL.   When I attempt to generate a map with this included, I
> get the error pasted below.
> 
>  
> 
> Any ideas?
> 
>  
> 
> Thanks,
> 
>  
> 
> Randy
> 
>  
> 
>  
> 
> msDrawMap(): Image handling error. Failed to draw layer named 'thematic'. 
> prep_DB(): Query error. Error executing POSTGIS DECLARE (the actual query)
> statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
> asbinary(force_collection(force_2d(PSx)),'NDR'),oid::text from PSx_poly
> WHERE PSx && setSRID('BOX3D(-79.1047 35.6605387954545,-78.90507
> 35.8056212045455)'::BOX3D, -1 )' 
> 
> Postgresql reports the error as 'ERROR: syntax error at or near "" at
> character 78 '
> 
> Mappostgis.c - version of Jan 23/2004. 
> msPOSTGISLayerParseData(): Query error. Error parsing POSTGIS data variable.
> Must contain 'geometry_column from table_name' or 'geom from (subselect) as
> foo' (couldnt find ' from '). More help: 
> 
> Error with POSTGIS data variable. You specified 'select the_geom FROM
> (SELECT tracts_c.the_geom AS the_geom, tracts_c.oid AS
> oid,"C2000_01_TR"."TOTPOP00" FROM tracts_c LEFT JOIN "C2000_01_TR" ON
> tracts_c.id = "C2000_01_TR"."NAME") AS foo using unique oid using SRID=-1'.
> Standard ways of specifiying are : 
> (1) 'geometry_column from geometry_table' 
> (2) 'geometry_column from (<sub query>) as foo using unique <column name>
> using SRID=<srid#>' 
> 
> Make sure you put in the 'using unique <column name>' and 'using SRID=#'
> clauses in.
> 
>  
> 
> 

Hello,

have you tried to look at the postgres log files. It usually gives 
better error messages and shows you the complete sql statements. Of 
course it depends how you started postgres. But if you find the 
errornous sql statement you could try to cut and paste it into psql to 
try the sql statemant and correct it if neccessary.

Henk