473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Help with trigger - URGENT!

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
8 2907
Suspicious. Call support.

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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Tolga Yaramis via SQLMonster.com | last post by:
Hi All I have a question about generating dynmamicly If Update() statement in a trigger.. in My db, there is a table that holds some column names of an another table. for example; Columns...
2
by: R. Rajesh Jeba Anbiah | last post by:
I have Googled a lot, but couldn't still find the answer... I could see, I can trigger the "click" event like: button_object.Click() But, I need to trigger the onChange() of select options. I...
0
by: Remove the obvious for replies | last post by:
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...
0
by: Johan Neidenmark | last post by:
When i try to run this SQL statements in iSeries Access for windows (against my customers db2) i get: SQL State: 42904 Vendor Code: -7032 Message: SQL procedure, function, or trigger...
2
by: Tian | last post by:
I want to create a trigger wherein with every insert on paymentdetails table the individual commission should be calculated for a particular agent based on his rank and the premium amt. At the same...
6
by: jenipriya | last post by:
Hi all... its very urgent.. please........i m a beginner in oracle.... Anyone please help me wit dese codes i hv tried... and correct the errors... The table structures i hav Employee (EmpID,...
2
by: jith87 | last post by:
hi, how do you truncate a table inside a trigger(plsql) if the table size is more than 1000 records??? regards, jith
11
by: tracy | last post by:
Hi, I really need help. I run this script and error message appeal as below: drop trigger log_errors_trig; drop trigger log_errors_trig ERROR at line 1: ORA04080: trigger 'LOG_ERRORS-TRIG'...
4
by: Peter | last post by:
ASP.NET 3.5 I have a web page and this web page has a DataList, when user hovers the mouse over a picture in the datalist a popup appers, this popup is a User Control and it has a cancel button....
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.