[postgis-users] Using CASE

[Bob Pawley]

I've tried -

 Update p_id.image
 set the_geom =
 (select st_translate(library.dgm_device.the_geom,
 st_x (st_centroid(p_id.p_id.othe_geom))  -
 st_x (st_centroid(library.dgm_device.ithe_geom)) ,
 st_y (st_centroid(p_id.p_id.othe_geom)) - (Select Case when 
p_id.devices.status = 'Pump #1'
 and p_id.devices.devices_id = '3614'
 then - 10000) -
 st_y (st_centroid(library.dgm_device.the_geom)))
 from p_id.p_id, library.dgm_device, p_id.processes, processes_count, 
p_id.devices
 where library.dgm_device.description = 'In Line Pump'
 and library.dgm_device.orientation = ta_orientation
 and p_id.p_id.p_id_id = processes_count.p_id_id
 and p_id.p_id.process_id = p_id.processes.process_id
 and p_id.devices.devices_id = '3614'
 and  p_id.processes.fluid_id = p_id.devices.fluid_id)
 from p_id.devices
 where p_id.image.fluid_id = p_id.devices.fluid_id
 and p_id.devices.devices_id = '3614'
 and p_id.image.text_ = 'Pump #1'
 and p_id.image.device_id = '3614';

and get ERROR:  syntax error at or near ")"
LINE 16:  then - 10000)

Bob
----- Original Message ----- 
From: "Stephen Woodbridge" <woodbri at swoodbridge.com>
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Sent: Thursday, February 04, 2010 2:13 PM
Subject: Re: [postgis-users] Using CASE


> Bob,
>
> You sub-select need to be enclosed in parens like:
>
>  ... - (select case ...) ...
>
> -Steve
>
> Bob Pawley wrote:
>> Hi
>>  I've been attempting to use CASE to establish the spatial point in the 
>> following expression.
>>  Update p_id.image
>>  set the_geom =  (select st_translate(library.dgm_device.the_geom, st_x 
>> (st_centroid(p_id.p_id.othe_geom))  -
>>  st_x (st_centroid(library.dgm_device.ithe_geom)) ,
>>  st_y (st_centroid(p_id.p_id.othe_geom)) - Select Case when 
>> p_id.devices.status = 'Pump #1'
>>  and p_id.devices.devices_id = '3614'
>>  then - 10000 -
>>  st_y (st_centroid(library.dgm_device.the_geom)))
>>  from p_id.p_id, library.dgm_device, p_id.processes, processes_count, 
>> p_id.devices where library.dgm_device.description = 'In Line Pump'
>>  and library.dgm_device.orientation = ta_orientation
>>  and p_id.p_id.p_id_id = processes_count.p_id_id
>>  and p_id.p_id.process_id = p_id.processes.process_id
>>  and p_id.devices.devices_id = '3614'
>>  and  p_id.processes.fluid_id = p_id.devices.fluid_id) from p_id.devices
>>  where p_id.image.fluid_id = p_id.devices.fluid_id
>>  and p_id.devices.devices_id = '3614'
>>  and p_id.image.text_ = 'Pump #1'
>>  and p_id.image.device_id = '3614';
>>  I get - "ERROR:  syntax error at or near "Select"
>> LINE 14:  st_y (st_centroid(p_id.p_id.othe_geom)"
>>  Any help would be appreciated.
>>  Bob
>>
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users