Simple. Brilliant. Obvious. Still doesn't work.
Here's what it looks like now:
/* Creating SQL trigger RJSFLOW.WORKPIECECLASSCOLUMNSLINKED */
CREATE TRIGGER RJSFLOW.WORKPIECECLASSCOLUMNSLINKED
AFTER INSERT ON RJSFLOW.WORKPIECECLASSCOLUMNS
FOR EACH ROW MODE DB2ROW
DELETE FROM RJSFLOW.WORKPIECECLASSCOLUMNSLINKED WHERE RJSFLOW.WORKPIECECLASSCOLUMNSLINKED."CLASS" = NEW_ROW."CLASS" ;
INSERT INTO RJSFLOW.WORKPIECECLASSCOLUMNSLINKED ( CLASS , N1 , N2 , N3 )
( SELECT CLAZZ."CLASS" , N1.NEXTVALUENAME , N2.NEXTVALUENAME , N3.NEXTVALUENAME FROM RJSFLOW.WORKPIECECLASS CLAZZ
LEFT JOIN RJSFLOW.WORKPIECECLASSCOLUMNS N1 ON CLAZZ."CLASS" = N1."CLASS" AND N1.VALUENAME = ''
LEFT JOIN RJSFLOW.WORKPIECECLASSCOLUMNS N2 ON CLAZZ."CLASS" = N2."CLASS" AND N2.VALUENAME = N1.NEXTVALUENAME
LEFT JOIN RJSFLOW.WORKPIECECLASSCOLUMNS N3 ON CLAZZ."CLASS" = N3."CLASS" AND N3.VALUENAME = N2.NEXTVALUENAME
WHERE CLAZZ."CLASS" = NEW_ROW."CLASS" ) ;
Here's the entire message:
SQL State: 42618
Vendor Code: -312
Message: [SQL0312] Variable "CLASS" not defined or not usable. Cause . . . . . : The variable "CLASS" appears in the SQL statement, but one of the following conditions exists: -- No declaration for the variable exists or the declaration is not within the current scope. -- The attributes are not correct for the use specified. -- The host variable was specified in dynamic SQL. Host variables are not valid in dynamic SQL. -- In REXX, host variable names cannot contain embedded blanks. -- The variable name is used in the routine body of an SQL procedure or function, but the variable is not declared as an SQL variable or parameter. The scope of an SQL variable is the compound statement that contains the declaration. -- The variable is used in the routine body of an SQL trigger, but the variable is not declared as an SQL variable or the variable is an OLD transition variable and cannot be modified. -- The variable is a transition variable in an AFTER trigger and is used in statement where the variable could be modified. Modifying transition variables in AFTER triggers is not allowed. Recovery . . . : Do one of the following and try the request again. -- Verify that "CLASS" is spelled correctly in the SQL statement. -- Verify that the program contains a declaration for that variable. In RPG, the variable must be declared globally or in the scope where it is used. -- Verify that the attributes of the variable are compatible with its use in the statement. -- Use parameter markers in dynamic SQL instead of host variables. -- Remove embedded blanks from REXX host variable names. -- Declare the variable as an SQL variable or parameter in the SQL procedure or function. -- Declare the variable as an SQL variable or specify a NEW transition variable when the variable is modified in an SQL trigger. -- Remove the transition variable from the statement. Copying the transition variable to a local variable and then using the local variable in the statement is also acceptable.
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Tuesday, February 05, 2013 2:10 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Trigger Body using "CLASS"
Try placing double quotes (") around the data name CLASS.
On Tue, Feb 5, 2013 at 3:06 PM, Dan Kimmel <dkimmel@xxxxxxxxxxxxxxx> wrote:
I have the following body to a trigger. It doesn't work anymore. "CLASS"
is now a reserved word. I have "CLASS" as a field name (column name)
in all the tables involved in the trigger body. Any ideas how I can
make this work without changing the field name in all the tables? The
trigger is after insert on workpiececlasscolumns.
DELETE FROM RJSFLOW . WORKPIECECLASSCOLUMNSLINKED WHERE RJSFLOW .
WORKPIECECLASSCOLUMNSLINKED . CLASS = NEW_ROW . CLASS ;
INSERT INTO RJSFLOW . WORKPIECECLASSCOLUMNSLINKED ( CLASS , N1 , N2 ,
N3 ) ( SELECT CLASS . CLASS , N1 . NEXTVALUENAME , N2 . NEXTVALUENAME , N3 .
NEXTVALUENAME FROM RJSFLOW . WORKPIECECLASS CLASS LEFT JOIN RJSFLOW .
WORKPIECECLASSCOLUMNS N1 ON CLASS . CLASS = N1 . CLASS AND N1 .
VALUENAME = ''
LEFT JOIN RJSFLOW . WORKPIECECLASSCOLUMNS N2 ON CLASS . CLASS = N2 .
CLASS AND N2 . VALUENAME = N1 . NEXTVALUENAME LEFT JOIN RJSFLOW .
WORKPIECECLASSCOLUMNS N3 ON CLASS . CLASS = N3 . CLASS AND N3 .
VALUENAME = N2 . NEXTVALUENAME WHERE CLASS . CLASS = NEW_ROW . CLASS )
Dan Kimmel | Workflow Guru | RJS Software Systems 2970 Judicial
Road, Suite 100 | Burnsville, MN 55337 | *: 952-736-5800 | *:
Manage the entire lifecycle of your business information
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
Sr. Programmer Analyst
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l