| View previous topic :: View next topic |
| Author |
Message |
drammo
Beginner
Joined: 13 Jan 2006
Posts: 20
Topics: 10
|
 Posted: Fri Jan 13, 2006 11:59 am Post subject: Create Trigger SQL -- Error |
 |
|
What is wrong with this query?
CREATE TRIGGER TRG1
AFTER UPDATE ON TABLE1
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE TABLE2
SET XCOL2 = 80.000;
END
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE:
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 433 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'000001B1' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE614I AUTOCOMMIT IS NO; NO CHANGES COMMITTED
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 6
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 22
******************************** Bottom of Data ******************************** |
|
| Back to top |
|
 |
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Fri Jan 13, 2006 12:18 pm Post subject: |
|
|
| Check that you SQL TERMINTATOR in the SPUFI DEFAULTS is not set to ";". |
|
| Back to top |
|
|
drammo
Beginner
Joined: 13 Jan 2006
Posts: 20
Topics: 10
|
| Posted: Fri Jan 13, 2006 12:32 pm Post subject: |
|
|
Yes it is, but I can space it out. SPUFI defaults does not accept spaces for terminator. If I leave the default as ";" and remove the terminator in my qurey I get:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "END". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ;
Thanks! |
|
| Back to top |
|
|
Bithead
Advanced
Joined: 03 Jan 2003
Posts: 550
Topics: 23
Location: Michigan, USA
|
| Posted: Fri Jan 13, 2006 12:34 pm Post subject: |
|
|
| Try using # for SQL TERMINATOR |
|
| Back to top |
|
|
drammo
Beginner
Joined: 13 Jan 2006
Posts: 20
Topics: 10
|
| Posted: Fri Jan 13, 2006 12:41 pm Post subject: |
|
|
It works. Thanks Bithead!
Applying all conditions to my previous query, I am getting a -199 on the "END IF;" statement. QMF also indicates an error on the same line. Can anyone help? Thanks.
| Code: |
CREATE TRIGGER TRG1
AFTER UPDATE ON TABLE1
REFERENCING OLD AS O NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.COL1 IN ('A', 'B') AND N.COL2 <> O.COL2)
BEGIN ATOMIC
IF N.COL2 = '01' THEN
UPDATE TABLE2 A
SET XCOL1 = XCOL9*0.80,
XCOL2 = 80
WHERE A.XCOL3 = N.COL3;
ELSEIF N.COL2 > '04' THEN
UPDATE TABLE2 A
SET XCOL1 = XCOL9*0.75,
XCOL2 = 75
WHERE A.XCOL3 = N.COL3;
ELSE
UPDATE TABLE2 A
SET XCOL1 = XCOL9,
XCOL2 = 100
WHERE A.XCOL3 = N.COL3;
END IF;
END
=======================================
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD IF, TOKEN
<END-OF-STATEMENT> WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 1734 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF'
X'000006C6' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+---------+
|
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
