CODEWITHSUNDEEP
db2substr
Ruben
Ruben

Reputation: 1091

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.

Upvotes: 1

Views: 5303

Answers (1)

user1919238
user1919238

Reputation:

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

Upvotes: 3

Related Questions