[postgis-users] Using CASE

[Bob Pawley]

OK thanks.

It appears that my original was missing end.

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:28 PM
Subject: Re: [postgis-users] Using CASE


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