I am using DB2 8.1.4 with SDE and Info Integrator. I am trying to
create a trigger to populate a spatial table based on values from an
attribute table. However, it appears to not recognize the CASE
condition. Here's the trigger :
create trigger MDA.TUA_AMNGROW NDIR after update
of UTM8315N,
UTM8315E
on MDA.MNGROWNDIR
referencing new as new_upd for each row mode db2sql
begin atomic
UPDATE DB2ADMIN.AUX_PT
SET shape =
CASE WHEN (new_upd.UTM831 5E IS NULL OR
new_upd.UTM8315 N IS NULL ) THEN NULL
ELSE
DB2GSE.ST_POINT (new_upd.UTM831 5E,new_upd.UTM8 315N,5)
END
WHERE OBJECTID = new_upd.OBJECTI D;
end%
What is supposed to happen is that if either the utm8315e or utm8315n
values are null, then the field name SHAPE in the spatial table should
be set to null. Otherwise, use the function gse.st_point() and update
the shape field with the results of the function (note: the function
cannot have null values in the parms).
If I try to set either utm8315e or utm8315n to null then, according to
the error, the db2gse.st_point is still being called so it would
appear that either the NULL condition is not being recognized or the
ELSE statement is firing every time.
I have tried to replace the else with NOT null consitions and the same
occurs. I have also tested the statement UPDATE db2admin.aux_pt set
shape = NULL where objectid = 2 and that woks OK (so shape is indeed
nullable)
Any ideas ?
create a trigger to populate a spatial table based on values from an
attribute table. However, it appears to not recognize the CASE
condition. Here's the trigger :
create trigger MDA.TUA_AMNGROW NDIR after update
of UTM8315N,
UTM8315E
on MDA.MNGROWNDIR
referencing new as new_upd for each row mode db2sql
begin atomic
UPDATE DB2ADMIN.AUX_PT
SET shape =
CASE WHEN (new_upd.UTM831 5E IS NULL OR
new_upd.UTM8315 N IS NULL ) THEN NULL
ELSE
DB2GSE.ST_POINT (new_upd.UTM831 5E,new_upd.UTM8 315N,5)
END
WHERE OBJECTID = new_upd.OBJECTI D;
end%
What is supposed to happen is that if either the utm8315e or utm8315n
values are null, then the field name SHAPE in the spatial table should
be set to null. Otherwise, use the function gse.st_point() and update
the shape field with the results of the function (note: the function
cannot have null values in the parms).
If I try to set either utm8315e or utm8315n to null then, according to
the error, the db2gse.st_point is still being called so it would
appear that either the NULL condition is not being recognized or the
ELSE statement is firing every time.
I have tried to replace the else with NOT null consitions and the same
occurs. I have also tested the statement UPDATE db2admin.aux_pt set
shape = NULL where objectid = 2 and that woks OK (so shape is indeed
nullable)
Any ideas ?