By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,694 Members | 1,882 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,694 IT Pros & Developers. It's quick & easy.

Help with trigger - URGENT!

P: n/a
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_AMNGROWNDIR 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.UTM8315E IS NULL OR
new_upd.UTM8315N IS NULL ) THEN NULL
ELSE

DB2GSE.ST_POINT(new_upd.UTM8315E,new_upd.UTM8315N, 5)
END
WHERE OBJECTID = new_upd.OBJECTID;
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 ?

Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Suspicious. Call support.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Suspicious. Call support.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
Remove the obvious for replies wrote:
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_AMNGROWNDIR 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.UTM8315E IS NULL OR
new_upd.UTM8315N IS NULL ) THEN NULL
ELSE

DB2GSE.ST_POINT(new_upd.UTM8315E,new_upd.UTM8315N, 5)
END
WHERE OBJECTID = new_upd.OBJECTID;
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 ?


As Serge said, there is no reason why this is failing and IBM support should
investigate this. For the time being, I can offer you a work-around which
might work (I haven't tested this myself, so no guarantees!)

CREATE TRIGGER ... AFTER UPDATE
OF ... ON ...
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE ...
SET shape = ( SELECT db2gse.ST_Point(n.utm8315e, n.utm8315n, 5)
FROM sysibm.sysdummy2
WHERE n.utm8315e IS NOT NULL AND
n.utm8315n IS NOT NULL )
WHERE objectid = n.objectid;

The idea is that a scalar subselect (used here) results in NULL if no row
qualifies/is produced. That way, you get implicitly the "shape" column set
to NULL in such a case.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4

P: n/a
Remove the obvious for replies wrote:
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_AMNGROWNDIR 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.UTM8315E IS NULL OR
new_upd.UTM8315N IS NULL ) THEN NULL
ELSE

DB2GSE.ST_POINT(new_upd.UTM8315E,new_upd.UTM8315N, 5)
END
WHERE OBJECTID = new_upd.OBJECTID;
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 ?


As Serge said, there is no reason why this is failing and IBM support should
investigate this. For the time being, I can offer you a work-around which
might work (I haven't tested this myself, so no guarantees!)

CREATE TRIGGER ... AFTER UPDATE
OF ... ON ...
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE ...
SET shape = ( SELECT db2gse.ST_Point(n.utm8315e, n.utm8315n, 5)
FROM sysibm.sysdummy2
WHERE n.utm8315e IS NOT NULL AND
n.utm8315n IS NOT NULL )
WHERE objectid = n.objectid;

The idea is that a scalar subselect (used here) results in NULL if no row
qualifies/is produced. That way, you get implicitly the "shape" column set
to NULL in such a case.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #5

P: n/a
Knut Stolze wrote:
CREATE TRIGGER ... AFTER UPDATE
OF ... ON ...
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE ...
SET shape = ( SELECT db2gse.ST_Point(n.utm8315e, n.utm8315n, 5)
FROM sysibm.sysdummy2
Ooops, that should be "sysibm.sysdummy1" here.
WHERE n.utm8315e IS NOT NULL AND
n.utm8315n IS NOT NULL )
WHERE objectid = n.objectid;

The idea is that a scalar subselect (used here) results in NULL if no row
qualifies/is produced. That way, you get implicitly the "shape" column
set to NULL in such a case.


And I forgot: the function in the SELECT clause is only called if the WHERE
clause causes the row to be qualified.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #6

P: n/a
Knut Stolze wrote:
CREATE TRIGGER ... AFTER UPDATE
OF ... ON ...
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE ...
SET shape = ( SELECT db2gse.ST_Point(n.utm8315e, n.utm8315n, 5)
FROM sysibm.sysdummy2
Ooops, that should be "sysibm.sysdummy1" here.
WHERE n.utm8315e IS NOT NULL AND
n.utm8315n IS NOT NULL )
WHERE objectid = n.objectid;

The idea is that a scalar subselect (used here) results in NULL if no row
qualifies/is produced. That way, you get implicitly the "shape" column
set to NULL in such a case.


And I forgot: the function in the SELECT clause is only called if the WHERE
clause causes the row to be qualified.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #7

P: n/a
Thanks knut!

I worked a solution already that I am real happy about. It uses 2
triggers with a when condition :

trigger1 :

WHEN (n.utm8315e IS NOT NULL and n.utm8315n IS NOT NULL)
BEGIN ATOMIC
UPDATE ...SET SHAPE = DB2GSE.ST_POINT( ...)
END

Trigger2 :
WHEN (n.utm8315e IS NULL OR n.utm8315n IS NULL)
BEGIN ATOMIC
UPDATE ...SET SHAPE = NULL
END

So it works, but I think I will call support because the first trigger
should have worked.

On Wed, 21 Apr 2004 13:16:47 +0200, Knut Stolze <st****@de.ibm.com>
wrote:
Knut Stolze wrote:
CREATE TRIGGER ... AFTER UPDATE
OF ... ON ...
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE ...
SET shape = ( SELECT db2gse.ST_Point(n.utm8315e, n.utm8315n, 5)
FROM sysibm.sysdummy2


Ooops, that should be "sysibm.sysdummy1" here.
WHERE n.utm8315e IS NOT NULL AND
n.utm8315n IS NOT NULL )
WHERE objectid = n.objectid;

The idea is that a scalar subselect (used here) results in NULL if no row
qualifies/is produced. That way, you get implicitly the "shape" column
set to NULL in such a case.


And I forgot: the function in the SELECT clause is only called if the WHERE
clause causes the row to be qualified.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena


Nov 12 '05 #8

P: n/a
Thanks knut!

I worked a solution already that I am real happy about. It uses 2
triggers with a when condition :

trigger1 :

WHEN (n.utm8315e IS NOT NULL and n.utm8315n IS NOT NULL)
BEGIN ATOMIC
UPDATE ...SET SHAPE = DB2GSE.ST_POINT( ...)
END

Trigger2 :
WHEN (n.utm8315e IS NULL OR n.utm8315n IS NULL)
BEGIN ATOMIC
UPDATE ...SET SHAPE = NULL
END

So it works, but I think I will call support because the first trigger
should have worked.

On Wed, 21 Apr 2004 13:16:47 +0200, Knut Stolze <st****@de.ibm.com>
wrote:
Knut Stolze wrote:
CREATE TRIGGER ... AFTER UPDATE
OF ... ON ...
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE ...
SET shape = ( SELECT db2gse.ST_Point(n.utm8315e, n.utm8315n, 5)
FROM sysibm.sysdummy2


Ooops, that should be "sysibm.sysdummy1" here.
WHERE n.utm8315e IS NOT NULL AND
n.utm8315n IS NOT NULL )
WHERE objectid = n.objectid;

The idea is that a scalar subselect (used here) results in NULL if no row
qualifies/is produced. That way, you get implicitly the "shape" column
set to NULL in such a case.


And I forgot: the function in the SELECT clause is only called if the WHERE
clause causes the row to be qualified.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena


Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.