[postgis-users] hitting a wall on netsted select

[Fred McDavid]

Dear Posterity,

For the unfortunates with similar problems who find this message with google 
or something similar down the road:

This silly problem is a result of SQL keywords being capitalized.  Change 
'FROM' to 'from' and 'AS' to 'as' (I kept the USING UNIQUE and USING SRID as 
capitals and I'm leaving it that way since it's working) and put one foot on 
a north-facing wall while smiling through half your mouth (it doesn't seem to 
matter which side) and the problem goes away.

Regards,

Fred

On Thursday 26 August 2004 12:54 pm, Fred McDavid wrote:
> Hi all,
>
> I've got a query that I'm able to make work (with minor appropriate
> modification) with pgadmin3, but that I can't get working with mapserver.
>
> Here's the query (I know it's bad form...it's autogenerated with a
> "chainable" query object):
>
> the_geom FROM (
>   select gid,the_geom from (
>     select gid,the_geom from staunton_parcels
>     )
>   as qry_id_1 where the_geom && GeometryFromText (
>     "POLYGON((670985.96343 4225879.55203,670985.96343
> 4225879.55203,670985.96343 4225879.55203,670985.96343 4225879.55203))",-1
>     )
>   ) AS qry_id_1_simplerenderer_param_tool_id USING UNIQUE gid USING SRID=-1
>
> In the error message I get, it appears that my query, at some point,
> becomes:
>
> DECLARE mycursor BINARY CURSOR FOR SELECT
> asbinary(force_collection(force_2d(the_geom FROM (select
> gid,the_geom)),'NDR'),gid::text from (select gid,the_geom from
> staunton_parcels) as qry_id_1 where the_geom &&
> GeometryFromText("POLYGON((670985.96343 4225879.55203,670985.96343
> 4225879.55203,670985.96343 4225879.55203,670985.96343 4225879.55203))",-1))
> AS qry_id_1_simplerenderer_param_tool_id WHERE the_geom FROM (select
> gid,the_geom && setSRID('BOX3D(670041.519546028
> 4225427.79642394,671593.10592686 4226432.50832267)'::BOX3D, -1 )
>
> The pgadmin3 query (that works and returns a result) is:
>
> select gid FROM (
>   select gid,the_geom from (
>     select gid,the_geom from staunton_parcels
>     )
>   as qry_id_1 where the_geom && GeometryFromText (
>     'POLYGON((670985.96343 4225879.55203,670985.96343
> 4225879.55203,670985.96343 4225879.55203,670985.96343 4225879.55203))',-1
>     )
>   ) AS qry_id_1_simplerenderer_param_tool_id
>
> Am I missing something obvious (as a new postgis and/or postgres user)?  Is
> there a limitation in the mapserver<->postgis connection that's going to
> make my nifty "chainable" queries unusable?
>
> Thanks in advance,
>
> --Fred

-- 
==========================================================
Fred R. McDavid, III
  540-248-0838
    frm at bitdaddy.com
      BitDaddy Systems, Inc
       * Complex System Design, Management, and Hosting
==========================================================