RE: SQL Trigger Body using "CLASS" -- MIDRANGE-L

It was my fat fingers all the time. Thanks to those who tried to help. I missed part of the procedure declaration and it had nothing at all to do with "CLASS" being a reserved word. Now I don't know why it stopped working all of a sudden. (Hard to tell how all-of-a-sudden, this is rarely used after setup.) This works:

/* Creating SQL trigger RJSFLOW.WORKPIECECLASSCOLUMNSLINKED */
CREATE TRIGGER RJSFLOW.WORKPIECECLASSCOLUMNSLINKED
AFTER INSERT ON RJSFLOW.WORKPIECECLASSCOLUMNS
referencing new as new_row
FOR EACH ROW MODE DB2ROW
BEGIN ATOMIC
DELETE FROM RJSFLOW.WORKPIECECLASSCOLUMNSLINKED linked where linked.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") ;
END ;

/* Creating SQL trigger RJSFLOW.WORKPIECECLASSCOLUMNSLINKED */
CREATE TRIGGER RJSFLOW.WORKPIECECLASSCOLUMNSLINKED
AFTER INSERT ON RJSFLOW.WORKPIECECLASSCOLUMNS
referencing new as new_row
FOR EACH ROW MODE DB2ROW
BEGIN ATOMIC
DELETE FROM RJSFLOW.WORKPIECECLASSCOLUMNSLINKED linked where linked.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") ;
END ;

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
Sent: Tuesday, February 05, 2013 4:35 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Trigger Body using "CLASS"

Didn't miss it. Tried it that way, too. Still no good.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, February 05, 2013 2:56 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Trigger Body using "CLASS"

Miss one?
INSERT INTO RJSFLOW.WORKPIECECLASSCOLUMNSLINKED ( CLASS , N1 , N2 , N3 )

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: Dan Kimmel <dkimmel@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 02/05/2013 03:53 PM
Subject: RE: SQL Trigger Body using "CLASS"
Sent by: midrange-l-bounces@xxxxxxxxxxxx

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 BEGIN ATOMIC 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" ) ; END ;

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 statem ent 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.

-----Original Message-----
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.
ie "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.

BEGIN ATOMIC
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 )
; END

______________________________________________________________________
________________________________________
Dan Kimmel | Workflow Guru | RJS Software Systems 2970 Judicial
Road, Suite 100 | Burnsville, MN 55337 | *: 952-736-5800 | *:
dkimmel@xxxxxxxxxxxxxxx<mailto:dkimmel@xxxxxxxxxxxxxxx>
[cid:907235719@05022013-183D]<http://www.rjssoftware.com/

[cid:907235719@05022013-1844]<mailto:http://

blogs.rjssoftware.com/rjsinformer/index.php/feed/

[cid:907235719@05022013-184B]<

http://www.facebook.com/?ref=home#!/pages/RJS-Software-Systems/1246876
44237867?ref=ts

[cid:907235719@05022013-1852]<http://twitter.com/rjssoft
[cid:907235719@05022013-1859]<

http://www.linkedin.com/companies/rjs-software-systems

[cid:907235719@05022013-1860]<

http://www.youtube.com/user/RJSSoftwareSystems>
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
Jeff Young
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.