Update Trigger Error

[lespaul]

I'm trying to create an update trigger for my table. When the record is updated with a case number, I need to insert some information in another table (CMPCASEDOC). One of the things I need to do prior to the insert is run a query against the CASEDOC table to see if there are any existing documents, the the max docseq that already exists and increment it. I have tried many ways of getting the correct information into the docseq variable, but keep getting this error:

[tt]
SQL0402N
The data type of an operand of an arithmetic function or operation operator is not numeric.
Explanation:
A nonnumeric operand is specified for the arithmetic function or operator operator.
The statement cannot be processed.[/tt]

When I ran the query alone, I got a null, which I then assumed was the problem since you can add 1 to null. The query that I currently have returns a zero when the record is null, but I still get the same error as above and can't increment.

Can someone help me to increment my docseq field?

Thanks!
Leslie

/* Creating SQL trigger LANDREWS.CMPCITMF_TO_CMPCASEDOC */
CREATE TRIGGER LANDREWS.CMPCITMF_TO_CMPCASEDOC 
AFTER UPDATE ON LANDREWS.CMPCITMF 
REFERENCING OLD O NEW N 
FOR EACH ROW  
MODE DB2ROW  
WHEN (O.CASPRE = '' AND N.CASPRE <> '')
 BEGIN 
 DECLARE NEWPATH VARCHAR ( 1000 ) ;
 DECLARE DOCNUM INTEGER;

[b]
 SET DOCNUM = (SELECT INTEGER(CASE WHEN MAX(DOCSEQ) IS NULL THEN 0 ELSE MAX(DOCSEQ) END) FROM LANDREWS.CMPCASEDOC WHERE CASPRE = N.CASPRE AND CASNUM = N.CASNUM);
 SET DOCNUM = DOCNUM + 1;[/b]
 
SET NEWPATH = (SELECT PATH FROM LANDREWS.CMPCITIMGS C INNER JOIN LANDREWS.CMPIMGPATH P ON C.DOCUMENTID = P.DOCUMENTID WHERE CITNUM = N.CITNUM);

IF NEWPATH <> '' THEN
  INSERT INTO LANDREWS.CMPCASEDOC (CASPRE, CASNUM, DOCSEQ, PATH, ENTRYDATE, ENTEREDBY, DISPTITLE, DOCTYPE) VALUES (N.CASPRE, N.CASNUM, DOCNUM, NEWPATH, N.VIOLDATE, 'LGA_TEST', N.CITNUM +'_N', 'CI');
END IF; 


 END;

 

[lespaul]

I figured it out.... the '+' sign it was referring to was in my insert clause (where I was trying to add the '_N' to the citation number....I need ||.....

Thanks!

Leslie

 

[MarcLodge]

Leslie,

So pleased we could be of help!

Ok, so we didn't exactly do much, but I'm sure we will next time!


Marc

 

[lespaul]

It was the forest for the trees....I just couldn't see that other + sign for about 2 hours....then suddenly it jumped out at me and I had a 'duh' moment!

I've gotten plenty of help in the past, so it's just as well that occasionally I help myself!

Leslie