OK thanks.
It appears that my original was missing end.
Bob
----- Original Message -----
From: "Stephen Woodbridge" <[email protected]>
To: "PostGIS Users Discussion" <[email protected]>
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"
<[email protected]>
To: "PostGIS Users Discussion" <[email protected]>
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
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
