I am writing an
INSTEAD OF trigger on a view. I need to split a string into 4 40 character long parts to insert into separate columns. I want to use
substr() to break the variable up into these parts, but when I try to create the trigger I get the error:
SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable SUBSTRING not found.
Cause . . . . . : SUBSTRING was not found as a column of table *N in *N and was not found as a global variable in *N. If the table is *N, SUBSTRING is not a column of any table or view that can be referenced.
Recovery . . . : Do one of the following and try the request again:
-- Ensure that the column and table names are specified correctly in the statement.
-- If this is a SELECT statement, ensure that all the required tables were named in the FROM clause.
-- If the column was intended to be a correlated reference, qualify the column with the correct table designator.
-- If the column was intended to be a global variable, qualify the name with the schema where the global variable exists or ensure the schema is in the path.
This page indicates that I can use an expression in my
USING clause as long as it is within a compound SQL statement. As I understand it, the SQL between
END in a trigger would be considered a compound SQL statement.
"An expression other than host-variable can only be used when the EXECUTE statement is used within a compound SQL (compiled) statement."
CREATE OR REPLACE TRIGGER MYLIB.INFO_INSERT INSTEAD OF INSERT ON MYLIB.INFO REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN DECLARE... ... set insert_into_info = 'INSERT INTO MYLIB.' || info || ' ( CICUST, CIINF1, CIINF2, CIINF3, CIINF4 ) VALUES (' || NEW_ID || ', ?, ?, ?, ? )'; PREPARE s1 from insert_into_info; EXECUTE s1 using substring(paddedComments, 1, 40), substring(paddedComments, 41, 80), substring(paddedComments, 81, 120), substring(paddedComments, 121, 160); END;
I have found examples for Oracle of using functions in the
USING clause, but no iSeries or DB2 examples, although there are enough hints in the descriptions of the
EXECUTE statement to keep me hoping. I am running z/OS 7.2 if that is relevant.
I've been searching around but haven't managed to figure this out; can anyone get me going in the right direction? I did also try to do this with an array so I wouldn't have to break up the string at the end, but I could not manage to get an array type declared and used within this trigger either.