[postgis-users] Using CASE

[Stephen Woodbridge]

Bob,

First thing it would really help us, and probably use if you indented 
you sql code so it is more readable:

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     --<<-- this is NOT a valid select!
            when p_id.devices.status = 'Pump #1'
                 and p_id.devices.devices_id = '3614'
            then - 10000  --<<-- what is "then - 1000"
        ) - 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';

the syntax is
CASE
   WHEN expression THEN expression
   [[WHEN expression THEN expression] ...]
   [ELSE expression]
END

Bob Pawley wrote:
> 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 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users