OGR with SELECT + INNER JOIN using MS/SQL views ?

[Lars V. Nielsen]

Hi list,

In Mapserver, I have a leyer containing polygons from a MapInfo table with
only a unique ID attached.

I want to append attributes from MS/SQL to each polygon, using the unique ID
to match a ZoneID in MS/SQL.

Unfortunately both the MapInfo table and the table on MS/SQL contain deleted
polygons and records resp., so I'm using a view to filter these out, based
on an "end date" attribute.

Unfortunately this doesn't work - with views that is. I tried to save the
view content as a table (i.e. ... 'ODBC:CFC-DB-R'.tblAZ ...), and then it
worked as expected !

Normally tables and views are generally interchangeable via ODBC, but there
seems to be a problem here, and I can't seem to get a grip on what it is.

Has anyone else tried this feature, and gotten it to work ?

My layer definition:

LAYER
 NAME "ZONES"
 PROJECTION
  "init=EPSG:25832"
 END
 Type POLYGON
 CONNECTIONTYPE OGR
 CONNECTION "./data/mi_zones.TAB"
 DATA "SELECT * FROM mi_zones b LEFT JOIN 'ODBC:CFC-DB-R'.vwActiveZones a ON
b.ID = a.ZoneID"
 STATUS ON
 CLASS
  EXPRESSION ('[ZoneID]' ne '')
  # (snip)
 END
END

Using Mapserver/MapScript 4.5 with Php 4.3.7 on Windows 2000.

GDAL is version 1.2.5 (gdal12.dll) from december 2004. I've tried to utilize
a later dated gdal library from FWTools, but to no avail.

What is the latest GDAL (with OGR) version suitable for Mapserver ?

Best regards / Med venlig hilsen
Lars V. Nielsen
--------------------------------------------------------
Hvenegaard A/S
Rugaardsvej 55, DK-5000 Odense C
Denmark
Tel. +45 6313 5050
http://www.hvenegaard.dk