[postgis-users] sql works, but not as layer?

[Twan Kogels]

Hello,

I've got 2 tables in my postgresql database, 1 table (pc6punten) contains 
zipcodes including a MULTIPOINT. The second table consists of infomation 
related to the zipcode.

Now i want to join both tables to display the zipcode points on a map (with 
mapserver).

So i constructed a sql query:
select the_geom, * from objecteigenschappen o left join pc6punten p on 
p.pc6=o.eigenschap_3 where (the_geom && GeometryFromText('BOX3D(109258 
559953,115258 565953)'::box3d,-1) and distance(the_geom, 
GeometryFromText('POINT(112258 562953)',-1)) < 3000)

When i execute this query directly in postgresql (with psql client) it 
works correct and returns the correct records.

But when i create a mapserver postgis layer in the map file:

CONNECTION "user=postgres dbname=food host=10.0.0.2"
DATA "the_geom from objecteigenschappen o left join pc6punten p on 
p.pc6=o.eigenschap_3"
FILTER "(the_geom && GeometryFromText('BOX3D(109258 559953,115258 
565953)'::box3d,-1) and distance(the_geom, GeometryFromText('POINT(112258 
562953)',-1)) < 3000)"

postgresql return a:
"ERROR:  Attribute 'oid' not found"
(i have made a oid index on the pc6punten and objecteigenschappen table)

mapserver returns a:
"Warning: MapServer Error in msPOSTGISLayerWhichShapes(): prep_DB:Error 
executing POSTGIS DECLARE statement (0.6 failed - retried 0.5 and it failed 
too). in /usr/local/apache/htdocs/food/food/kaart.php on line x"

A mapserver postgis layer like:

CONNECTION "user=postgres dbname=food host=10.0.0.2"
DATA "the_geom from pc6punten"
FILTER "pc6 like '1797%'

returns without errors and with the correct results.

I suspect the error is related with the "left join" function, but i can't 
seem to find what i'm doing wrong.

Any ideas what is going wrong?

Best regards,
-twan

-- 
http://www.twansoft.com