I get the following error trying to create a stored procedure in DB2
Error report:
DB2 SQL error: SQLCODE: -138, SQLSTATE: 22011, SQLERRMC: null
My trigger is the following one:
CREATE TRIGGER INSERT_SERIALNUMBER
AFTER INSERT ON LASERM
REFERENCING NEW ROW AS NROW
FOR EACH ROW MODE DB2SQL
BEGIN
DECLARE ARTICLECODE CHAR(30);
DECLARE POS INT;
SET POS = LOCATE('-', NROW.PROGRAMNAME);
IF POS > 0 THEN
SET ARTICLECODE = SUBSTR(NROW.PROGRAMNAME, 0, POS);
ELSE
SET ARTICLECODE = NROW.PROGRAMNAME;
END IF;
CALL SP_INSERT_SERIALNUMBER(ARTICLECODE, NROW.PCBCODE);
END
According to the DB2 SQLCodes list, -138
-138 THE SECOND OR THIRD ARGUMENT OF THE SUBSTR OR SUBSTRING FUNCTION IS OUT OF RANGE
What I want to do is to substract a string from another when it finds a "-". For example, the code is "ART00001-A" and I would like to get just "ART00001".
I'm not an expert in DB2 SQL syntax, so please,thanks in advance if you see the problem in that code.
String positions in DB2 are one-based, but you are indicating a substring that starts at position 0. Just use:
SET ARTICLECODE = SUBSTR(NROW.PROGRAMNAME, 1, POS);