[SOLVED] Error SQLCODE -138 when doing a trigger in DB2

Error SQLCODE -138 when doing a trigger in DB2

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.

Solution

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);