can't do postgis subselect in DATA

[Newland]

try
DATA "the_geom from (select capolygon.oid, the_geom, blah, blah2 from
capolygon,othertable
where capolygon.colA = othertable.colB) as foo using srid=-1 using unique
oid"

note that I added the capolygon.oid as part of the initial select.


-----Original Message-----
From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU]On
Behalf Of Brent Pedersen
Sent: Wednesday, June 22, 2005 11:50 AM
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: [UMN_MAPSERVER-USERS] can't do postgis subselect in DATA


hi,
using:
___php 5.0.4 './configure' '--with-mysql' '--enable-cgi' '--with-xml'
'--with-zlib' '--with-pgsql' '--with-gd=/usr' '--with-png' '--with-jpeg'
'--enable-wddx' '--enable-dbase' '--with-regex=system'
'--with-freetype-dir=/usr' '--enable-force-cgi-redirect'

___MapServer version 4.4.2 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP
OUTPUT=PDF SUPPORTS=PROJ SUPPORTS=FREETYPE SUPPORTS=WMS_SERVER INPUT=EPPL7
INPUT=POSTGIS INPUT=GDAL INPUT=SHAPEFILE
___php mapscript($Revision: 1.220.2.3 $ $Date: 2005/02/28 14:27:19 $)
___postgis 1.0.0

i do this and it works fine:
DATA "the_geom from capolygon using srid=-1, using unique oid"

anything more complicated fails.
even if i just add parentheses it failes:

DATA "the_geom from (capolygon) using srid=-1, using unique oid"

when what i actually want is something more complex

DATA "the_geom from (select the_geom, blah, blah2 from capolygon,othertable
where capolygon.colA = othertable.colB) as foo using srid=-1, using unique
oid"

the last query works fine from the psql command line. however as soon as i
add
any special characters: '  "  (    in the DATA line it won't work.

does anyone have any idea what's going on? thanks,
-brent


here's my simple data statement with ()'s. if i remove the ()'s it works.

DATA  "the_geom FROM (capolygon) using unique gid using srid=-1"

here's the error output:

[22-Jun-2005 11:45:18] PHP Warning:  [MapServer Error]: msDrawMap(): Failed
to
draw layer named 'Pesticide Use'.
 in /opt/chameleon/htdocs/common/wrapper/drawmap.php on line 533
[22-Jun-2005 11:45:18] PHP Warning:  [MapServer Error]: prepare_database():
Error executing POSTGISDECLARE (the actual query) statement: 'DECLARE
mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from
(capolygon) WHERE the_geom && setSRID('BOX3D(-366327.8 -614696,536327.8
459894)'::BOX3D, -1 )' <br><br>

Postgresql reports the error as 'ERROR:  syntax error at or near ")" at
character 121