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.
midrange.com
