I have two tables. One has a nullable foreign key of the other but the stored procedure that inserts into the records into the child table fails: Error occured: [IBM][CLI Driver][DB2] SQL0530N The insert or update value of the FOREIGN KEY "FKSSRTSK" is not equal to any value of the parent key of the parent table. SQLSTATE=23503
I tend to think an invalid value is being inserted that violates te constraint, but I don't see how. Should I declare the foreign key in the child as Default Null?
Here is the ddl for the two tables:
CREATE TABLE TALRT (
SYS_TSK_ID INTEGER NOT NULL,
APP_NM INTEGER,
CUR_STAT_IND CHAR(1) NOT NULL,
SPCL_PROC_IND CHAR(1) NOT NULL,
TSK_DESC VARCHAR(250),
TSK_TYP_ID INTEGER NOT NULL,
ALOW_RCUR_IND CHAR(1),
ALOW_EXT_IND CHAR(1),
SUBJ_TXT VARCHAR(250),
TSK_NT_ATCH_TXT VARCHAR(500),
DTL_LNK_TXT VARCHAR(500),
TSK_STAT_ID INTEGER,
TSK_ASGN_ID_TXT VARCHAR(250) NOT NULL,
DUE_DY_CT INTEGER NOT NULL,
KEY_FLD_IND_NM VARCHAR(250) NOT NULL,
OPN_CALL_OBJ_ADR VARCHAR(500) NOT NULL,
ADD_SUBJ_TXT VARCHAR(1000),
ADD_CNTY_ID VARCHAR(2),
ADD_ZIP_CD VARCHAR(10),
SUPV_ELEV_DY_CT INTEGER,
SUPV_ELEV_TYP_ID INTEGER,
SUPV_CMPL_NTFY_IND CHAR(1),
DIR_ELEV_DY_CT INTEGER,
DIR_ELEV_TYP_ID INTEGER,
DIR_CMPL_NTFY_IND CHAR(1),
STOF_ELEV_DY_CT INTEGER,
STOF_ELEV_TYP_ID INTEGER,
STOF_CMPL_NTFY_IND CHAR(1),
PRIOR_ID INTEGER,
CREATE_DT TIMESTAMP NOT NULL,
CREATE_USER CHAR(8) NOT NULL,
CREATE_PGM CHAR(8) NOT NULL,
UPDATE_DT TIMESTAMP,
UPDATE_USER CHAR(8),
UPDATE_PGM CHAR(8),
PRIMARY KEY (SYS_TSK_ID)
);
CREATE UNIQUE INDEX XPKTALRT ON TALRT
(
SYS_TSK_ID ASC
);
CREATE TABLE TUSER_TSK_LIST (
USER_TSK_ID INTEGER NOT NULL,
SYS_TSK_ID INTEGER NOT NULL,
TSK_SUBJ_TXT VARCHAR(200),
TSK_PRIOR_ID INTEGER,
TSK_STAT_ID INTEGER,
TSK_TYP_ID INTEGER NOT NULL,
CUR_TSK_ST_IND CHAR(1) NOT NULL,
TSK_CLS_DT DATE,
DTL_LNK_TXT VARCHAR(255),
TBL_COL_NM VARCHAR(150),
TBL_COL_DAT_ID VARCHAR(50),
OPN_SCRN_ID VARCHAR(255),
CMNT_TXT VARCHAR(255),
TSK_DUE_DT DATE NOT NULL,
ASGN_USER_ID VARCHAR(10) NOT NULL,
TSK_ASGN_DT DATE NOT NULL,
NXT_TARG_DT DATE,
TSK_CMPL_DT DATE,
RQST_XTN_IND CHAR(1),
RQST_XTN_DT DATE,
XTN_APRV_ID VARCHAR(10),
XTN_APRV_DT DATE,
RQST_FWRD_IND CHAR(1),
RQST_CLS_IND CHAR(1),
RQST_USER_TSK_ID INTEGER,
CREATE_DT TIMESTAMP NOT NULL,
CREATE_USER CHAR(8) NOT NULL,
CREATE_PGM CHAR(8) NOT NULL,
UPDATE_DT TIMESTAMP,
UPDATE_USER CHAR(8),
UPDATE_PGM CHAR(8),
PRIMARY KEY (USER_TSK_ID)
);
CREATE UNIQUE INDEX XPKTUSER_TSK_LIST ON TUSER_TSK_LIST
(
USER_TSK_ID ASC
);
CREATE VIEW VALRT AS
SELECT *
FROM TALRT;
CREATE VIEW TVUSR_TSK_LIST AS
SELECT *
FROM TUSER_TSK_LIST;
ALTER TABLE TUSER_TSK_LIST
ADD FOREIGN KEY (SYS_TSK_ID)
REFERENCES TALRT
ON DELETE RESTRICT;
Here is the stored procedure
CREATE PROCEDURE CC000.TUSERTSKLISTUID
(
IN UserTaskID INTEGER,
IN SysTaskID INTEGER,
IN SubjectText VARCHAR (200),
IN PriorityID INTEGER,
IN TaskStatusID INTEGER,
IN TaskTypeID INTEGER,
IN CurrentStatusInd CHAR (1),
IN TaskCloseDate DATE,
IN DetailLinkText VARCHAR (255),
IN TableColumnName VARCHAR (150),
IN TableColumnDataID VARCHAR (50),
IN OpenCallObjectAdr VARCHAR (255),
IN CommentText VARCHAR (255),
IN TaskDueDate DATE,
IN AsgnUserID VARCHAR (10),
IN TaskAsgnDate DATE,
IN NextTargetDate DATE,
IN TaskCmplDate DATE,
IN RqstExtensionInd CHAR (1),
IN RqstExtensionDate DATE,
IN ExtensionAprvID VARCHAR (10),
IN ExtensionAprvDate DATE,
IN RqstForwardInd CHAR (1),
IN RqstCloseInd CHAR (1),
IN RqstUserTaskID INTEGER,
IN CreateDate TIMESTAMP,
IN CreateUser CHAR (8),
IN CreateProgram CHAR (8),
IN UpdateDate TIMESTAMP,
IN UpdateUser CHAR (8),
IN UpdateProgram CHAR (8)
)
RESULT SETS 1
LANGUAGE SQL
COLLID DBIT
WLM ENVIRONMENT DBITENV
SECURITY USER
RUN OPTIONS ''
P1: BEGIN
-- *****BEGIN DECLARATIONS*****
--declare local variables
DECLARE lclUSER_TSK_ID INTEGER;
DECLARE lclSYS_TSK_ID INTEGER;
DECLARE lclTSK_SUBJ_TXT VARCHAR (200);
DECLARE lclTSK_PRIOR_ID INTEGER;
DECLARE lclTSK_STAT_ID INTEGER;
DECLARE lclTSK_TYP_ID INTEGER;
DECLARE lvCUR_TSK_STAT_IND CHAR (1);
DECLARE lclTSK_CLS_DT DATE;
DECLARE lclDTL_LNK_TXT VARCHAR (255);
DECLARE lclTBL_COL_NM VARCHAR (150);
DECLARE lclTBL_COL_DAT_ID VARCHAR (50);
DECLARE lclOPN_SCRN_ID VARCHAR (255);
DECLARE lclCMNT_TXT VARCHAR (255);
DECLARE lclTSK_DUE_DT DATE;
DECLARE lclASGN_USER_ID VARCHAR (10);
DECLARE lclTSK_ASGN_DT DATE;
DECLARE lclNXT_TARG_DT DATE;
DECLARE lclTSK_CMPL_DT DATE;
DECLARE lclRQST_EXT_IND CHAR (1);
DECLARE lclRQST_EXT_DT DATE;
DECLARE lclEXT_APRV_ID VARCHAR (10);
DECLARE lclEXT_APRV_DT DATE;
DECLARE lclRQST_FWRD_IND CHAR (1);
DECLARE lclRQST_CLS_IND CHAR (1);
DECLARE lvRQST_USER_TSK_ID INTEGER;
DECLARE lclCREATE_DT TIMESTAMP;
DECLARE lclCREATE_USER CHAR (8);
DECLARE lclCREATE_PGM CHAR (8);
DECLARE lclUPDATE_DT TIMESTAMP;
DECLARE lclUPDATE_USER CHAR (8);
DECLARE lclUPDATE_PGM CHAR (8);
-- Declare cursor to return updated record to client
DECLARE DB2_SQL1 CURSOR WITH RETURN FOR
SELECT
VUSERTSKLIST.USER_TSK_ID AS "UserTaskID",
VUSERTSKLIST.SYS_TSK_ID AS "SysTaskID",
VUSERTSKLIST.TSK_SUBJ_TXT AS "SubjectText",
VUSERTSKLIST.TSK_PRIOR_ID AS "PriorityID",
VUSERTSKLIST.TSK_STAT_ID AS "TaskStatusID",
VUSERTSKLIST.TSK_TYP_ID AS "TaskTypeID",
VUSERTSKLIST.CUR_TSK_STAT_IND AS "CurrentStatusInd",
VUSERTSKLIST.TSK_CLS_DT AS "TaskCloseDate",
VUSERTSKLIST.DTL_LNK_TXT AS "DetailLinkText",
VUSERTSKLIST.TBL_COL_NM AS "TableColumnName",
VUSERTSKLIST.TBL_COL_DAT_ID AS "TableColumnDataID",
VUSERTSKLIST.OPN_SCRN_ID AS "OpenCallObjectAdr",
VUSERTSKLIST.CMNT_TXT AS "CommentText",
VUSERTSKLIST.TSK_DUE_DT AS "TaskDueDate",
VUSERTSKLIST.ASGN_USER_ID AS "AsgnUserID",
VUSERTSKLIST.TSK_ASGN_DT AS "TaskAsgnDate",
VUSERTSKLIST.NXT_TARG_DT AS "NextTargetDate",
VUSERTSKLIST.TSK_CMPL_DT AS "TaskCmplDate",
VUSERTSKLIST.RQST_EXT_IND AS "RqstExtensionInd",
VUSERTSKLIST.RQST_EXT_DT AS "RqstExtensionDate",
VUSERTSKLIST.EXT_APRV_ID AS "ExtensionAprvID",
VUSERTSKLIST.EXT_APRV_DT AS "ExtensionAprvDate",
VUSERTSKLIST.RQST_FWRD_IND AS "RqstForwardInd",
VUSERTSKLIST.RQST_CLS_IND AS "RqstCloseInd",
VUSERTSKLIST.RQST_USER_TSK_ID AS "RqstUserTaskID",
VUSERTSKLIST.CREATE_DT AS "CreateDate",
VUSERTSKLIST.CREATE_USER AS "CreateUser",
VUSERTSKLIST.CREATE_PGM AS "CreateProgram",
VUSERTSKLIST.UPDATE_DT AS "UpdateDate",
VUSERTSKLIST.UPDATE_USER AS "UpdateUser",
VUSERTSKLIST.UPDATE_PGM AS "UpdateProgram"
FROM CC000.VUSER_TSK_LIST AS VUSERTSKLIST
WHERE
USER_TSK_ID=lclUSER_TSK_ID;
-- *****END DECLARATIONS*****
-- Set local variables to parameter values
SET lclUSER_TSK_ID=UserTaskID;
SET lclSYS_TSK_ID=SysTaskID;
SET lclTSK_SUBJ_TXT=SubjectText;
SET lclTSK_PRIOR_ID=PriorityID;
SET lclTSK_STAT_ID=TaskStatusID;
SET lclTSK_TYP_ID=TaskTypeID;
SET lvCUR_TSK_STAT_IND=CurrentStatusInd;
SET lclTSK_CLS_DT=TaskCloseDate;
SET lclDTL_LNK_TXT=DetailLinkText;
SET lclTBL_COL_NM=TableColumnName;
SET lclTBL_COL_DAT_ID=TableColumnDataID;
SET lclOPN_SCRN_ID=OpenCallObjectAdr;
SET lclCMNT_TXT=CommentText;
SET lclTSK_DUE_DT=TaskDueDate;
SET lclASGN_USER_ID=AsgnUserID;
SET lclTSK_ASGN_DT=TaskAsgnDate;
SET lclNXT_TARG_DT=NextTargetDate;
SET lclTSK_CMPL_DT=TaskCmplDate;
SET lclRQST_EXT_IND=RqstExtensionInd;
SET lclRQST_EXT_DT=RqstExtensionDate;
SET lclEXT_APRV_ID=ExtensionAprvID;
SET lclEXT_APRV_DT=ExtensionAprvDate;
SET lclRQST_FWRD_IND=RqstForwardInd;
SET lclRQST_CLS_IND=RqstCloseInd;
SET lvRQST_USER_TSK_ID=RqstUserTaskID;
SET lclCREATE_DT=CreateDate;
SET lclCREATE_USER=CreateUser;
SET lclCREATE_PGM=CreateProgram;
SET lclUPDATE_DT=UpdateDate;
SET lclUPDATE_USER=UpdateUser;
SET lclUPDATE_PGM=UpdateProgram;
-- test for delete, if delete is selected, delete record
--IF DELETE='y' or delete='Y' THEN
-- DELETE FROM CC000.VUSER_TSK_LIST
-- WHERE
-- USER_TSK_ID=lclUSER_TSK_ID;
-- test for exists, if does, then update, else insert
--ELSEIF EXISTS
IF EXISTS
(
SELECT USER_TSK_ID
FROM CC000.VUSER_TSK_LIST
WHERE
USER_TSK_ID=lclUSER_TSK_ID
) THEN
-- **TODO** (#5)
-- Update SQL statement for history table
--
UPDATE CC000.VUSER_TSK_LIST SET
SYS_TSK_ID=lclSYS_TSK_ID,
TSK_SUBJ_TXT=lclTSK_SUBJ_TXT,
TSK_PRIOR_ID=lclTSK_PRIOR_ID,
TSK_STAT_ID=lclTSK_STAT_ID,
TSK_TYP_ID=lclTSK_TYP_ID,
CUR_TSK_STAT_IND=lvCUR_TSK_STAT_IND,
TSK_CLS_DT=lclTSK_CLS_DT,
DTL_LNK_TXT=lclDTL_LNK_TXT,
TBL_COL_NM=lclTBL_COL_NM,
TBL_COL_DAT_ID=lclTBL_COL_DAT_ID,
OPN_SCRN_ID=lclOPN_SCRN_ID,
CMNT_TXT=lclCMNT_TXT,
TSK_DUE_DT=lclTSK_DUE_DT,
ASGN_USER_ID=lclASGN_USER_ID,
TSK_ASGN_DT=lclTSK_ASGN_DT,
NXT_TARG_DT=lclNXT_TARG_DT,
TSK_CMPL_DT=lclTSK_CMPL_DT,
RQST_EXT_IND=lclRQST_EXT_IND,
RQST_EXT_DT=lclRQST_EXT_DT,
EXT_APRV_ID=lclEXT_APRV_ID,
EXT_APRV_DT=lclEXT_APRV_DT,
RQST_FWRD_IND=lclRQST_FWRD_IND,
RQST_CLS_IND=lclRQST_CLS_IND,
RQST_USER_TSK_ID=lvRQST_USER_TSK_ID,
--CREATE_DT=lclCREATE_DT,
--CREATE_USER=lclCREATE_USER,
--CREATE_PGM=lclCREATE_PGM,
UPDATE_DT=lclUPDATE_DT,
UPDATE_USER=lclUPDATE_USER,
UPDATE_PGM=lclUPDATE_PGM
WHERE
USER_TSK_ID=lclUSER_TSK_ID;
ELSE
INSERT INTO CC000.VUSER_TSK_LIST(SYS_TSK_ID, TSK_SUBJ_TXT, TSK_PRIOR_ID, TSK_STAT_ID, TSK_TYP_ID, CUR_TSK_STAT_IND, TSK_CLS_DT, DTL_LNK_TXT, TBL_COL_NM, TBL_COL_DAT_ID, OPN_SCRN_ID, CMNT_TXT, TSK_DUE_DT, ASGN_USER_ID, TSK_ASGN_DT, NXT_TARG_DT, TSK_CMPL_DT, RQST_EXT_IND, RQST_EXT_DT, EXT_APRV_ID, EXT_APRV_DT, RQST_FWRD_IND, RQST_CLS_IND, RQST_USER_TSK_ID, CREATE_DT, CREATE_USER, CREATE_PGM, UPDATE_DT, UPDATE_USER, UPDATE_PGM)
VALUES(lclSYS_TSK_ID, lclTSK_SUBJ_TXT, lclTSK_PRIOR_ID, lclTSK_STAT_ID, lclTSK_TYP_ID, lvCUR_TSK_STAT_IND, lclTSK_CLS_DT, lclDTL_LNK_TXT, lclTBL_COL_NM, lclTBL_COL_DAT_ID, lclOPN_SCRN_ID, lclCMNT_TXT, lclTSK_DUE_DT, lclASGN_USER_ID, lclTSK_ASGN_DT, lclNXT_TARG_DT, lclTSK_CMPL_DT, lclRQST_EXT_IND, lclRQST_EXT_DT, lclEXT_APRV_ID, lclEXT_APRV_DT, lclRQST_FWRD_IND, lclRQST_CLS_IND, lvRQST_USER_TSK_ID, CURRENT TIMESTAMP, lclCREATE_USER, lclCREATE_PGM, CURRENT TIMESTAMP, lclUPDATE_USER, lclUPDATE_PGM);
SET lclUSER_TSK_ID = IDENTITY_VAL_LOCAL();
END IF;
-- Cursor left open for client application.
OPEN DB2_SQL1;
END P1
I tend to think an invalid value is being inserted that violates te constraint, but I don't see how. Should I declare the foreign key in the child as Default Null?
Here is the ddl for the two tables:
CREATE TABLE TALRT (
SYS_TSK_ID INTEGER NOT NULL,
APP_NM INTEGER,
CUR_STAT_IND CHAR(1) NOT NULL,
SPCL_PROC_IND CHAR(1) NOT NULL,
TSK_DESC VARCHAR(250),
TSK_TYP_ID INTEGER NOT NULL,
ALOW_RCUR_IND CHAR(1),
ALOW_EXT_IND CHAR(1),
SUBJ_TXT VARCHAR(250),
TSK_NT_ATCH_TXT VARCHAR(500),
DTL_LNK_TXT VARCHAR(500),
TSK_STAT_ID INTEGER,
TSK_ASGN_ID_TXT VARCHAR(250) NOT NULL,
DUE_DY_CT INTEGER NOT NULL,
KEY_FLD_IND_NM VARCHAR(250) NOT NULL,
OPN_CALL_OBJ_ADR VARCHAR(500) NOT NULL,
ADD_SUBJ_TXT VARCHAR(1000),
ADD_CNTY_ID VARCHAR(2),
ADD_ZIP_CD VARCHAR(10),
SUPV_ELEV_DY_CT INTEGER,
SUPV_ELEV_TYP_ID INTEGER,
SUPV_CMPL_NTFY_IND CHAR(1),
DIR_ELEV_DY_CT INTEGER,
DIR_ELEV_TYP_ID INTEGER,
DIR_CMPL_NTFY_IND CHAR(1),
STOF_ELEV_DY_CT INTEGER,
STOF_ELEV_TYP_ID INTEGER,
STOF_CMPL_NTFY_IND CHAR(1),
PRIOR_ID INTEGER,
CREATE_DT TIMESTAMP NOT NULL,
CREATE_USER CHAR(8) NOT NULL,
CREATE_PGM CHAR(8) NOT NULL,
UPDATE_DT TIMESTAMP,
UPDATE_USER CHAR(8),
UPDATE_PGM CHAR(8),
PRIMARY KEY (SYS_TSK_ID)
);
CREATE UNIQUE INDEX XPKTALRT ON TALRT
(
SYS_TSK_ID ASC
);
CREATE TABLE TUSER_TSK_LIST (
USER_TSK_ID INTEGER NOT NULL,
SYS_TSK_ID INTEGER NOT NULL,
TSK_SUBJ_TXT VARCHAR(200),
TSK_PRIOR_ID INTEGER,
TSK_STAT_ID INTEGER,
TSK_TYP_ID INTEGER NOT NULL,
CUR_TSK_ST_IND CHAR(1) NOT NULL,
TSK_CLS_DT DATE,
DTL_LNK_TXT VARCHAR(255),
TBL_COL_NM VARCHAR(150),
TBL_COL_DAT_ID VARCHAR(50),
OPN_SCRN_ID VARCHAR(255),
CMNT_TXT VARCHAR(255),
TSK_DUE_DT DATE NOT NULL,
ASGN_USER_ID VARCHAR(10) NOT NULL,
TSK_ASGN_DT DATE NOT NULL,
NXT_TARG_DT DATE,
TSK_CMPL_DT DATE,
RQST_XTN_IND CHAR(1),
RQST_XTN_DT DATE,
XTN_APRV_ID VARCHAR(10),
XTN_APRV_DT DATE,
RQST_FWRD_IND CHAR(1),
RQST_CLS_IND CHAR(1),
RQST_USER_TSK_ID INTEGER,
CREATE_DT TIMESTAMP NOT NULL,
CREATE_USER CHAR(8) NOT NULL,
CREATE_PGM CHAR(8) NOT NULL,
UPDATE_DT TIMESTAMP,
UPDATE_USER CHAR(8),
UPDATE_PGM CHAR(8),
PRIMARY KEY (USER_TSK_ID)
);
CREATE UNIQUE INDEX XPKTUSER_TSK_LIST ON TUSER_TSK_LIST
(
USER_TSK_ID ASC
);
CREATE VIEW VALRT AS
SELECT *
FROM TALRT;
CREATE VIEW TVUSR_TSK_LIST AS
SELECT *
FROM TUSER_TSK_LIST;
ALTER TABLE TUSER_TSK_LIST
ADD FOREIGN KEY (SYS_TSK_ID)
REFERENCES TALRT
ON DELETE RESTRICT;
Here is the stored procedure
CREATE PROCEDURE CC000.TUSERTSKLISTUID
(
IN UserTaskID INTEGER,
IN SysTaskID INTEGER,
IN SubjectText VARCHAR (200),
IN PriorityID INTEGER,
IN TaskStatusID INTEGER,
IN TaskTypeID INTEGER,
IN CurrentStatusInd CHAR (1),
IN TaskCloseDate DATE,
IN DetailLinkText VARCHAR (255),
IN TableColumnName VARCHAR (150),
IN TableColumnDataID VARCHAR (50),
IN OpenCallObjectAdr VARCHAR (255),
IN CommentText VARCHAR (255),
IN TaskDueDate DATE,
IN AsgnUserID VARCHAR (10),
IN TaskAsgnDate DATE,
IN NextTargetDate DATE,
IN TaskCmplDate DATE,
IN RqstExtensionInd CHAR (1),
IN RqstExtensionDate DATE,
IN ExtensionAprvID VARCHAR (10),
IN ExtensionAprvDate DATE,
IN RqstForwardInd CHAR (1),
IN RqstCloseInd CHAR (1),
IN RqstUserTaskID INTEGER,
IN CreateDate TIMESTAMP,
IN CreateUser CHAR (8),
IN CreateProgram CHAR (8),
IN UpdateDate TIMESTAMP,
IN UpdateUser CHAR (8),
IN UpdateProgram CHAR (8)
)
RESULT SETS 1
LANGUAGE SQL
COLLID DBIT
WLM ENVIRONMENT DBITENV
SECURITY USER
RUN OPTIONS ''
P1: BEGIN
-- *****BEGIN DECLARATIONS*****
--declare local variables
DECLARE lclUSER_TSK_ID INTEGER;
DECLARE lclSYS_TSK_ID INTEGER;
DECLARE lclTSK_SUBJ_TXT VARCHAR (200);
DECLARE lclTSK_PRIOR_ID INTEGER;
DECLARE lclTSK_STAT_ID INTEGER;
DECLARE lclTSK_TYP_ID INTEGER;
DECLARE lvCUR_TSK_STAT_IND CHAR (1);
DECLARE lclTSK_CLS_DT DATE;
DECLARE lclDTL_LNK_TXT VARCHAR (255);
DECLARE lclTBL_COL_NM VARCHAR (150);
DECLARE lclTBL_COL_DAT_ID VARCHAR (50);
DECLARE lclOPN_SCRN_ID VARCHAR (255);
DECLARE lclCMNT_TXT VARCHAR (255);
DECLARE lclTSK_DUE_DT DATE;
DECLARE lclASGN_USER_ID VARCHAR (10);
DECLARE lclTSK_ASGN_DT DATE;
DECLARE lclNXT_TARG_DT DATE;
DECLARE lclTSK_CMPL_DT DATE;
DECLARE lclRQST_EXT_IND CHAR (1);
DECLARE lclRQST_EXT_DT DATE;
DECLARE lclEXT_APRV_ID VARCHAR (10);
DECLARE lclEXT_APRV_DT DATE;
DECLARE lclRQST_FWRD_IND CHAR (1);
DECLARE lclRQST_CLS_IND CHAR (1);
DECLARE lvRQST_USER_TSK_ID INTEGER;
DECLARE lclCREATE_DT TIMESTAMP;
DECLARE lclCREATE_USER CHAR (8);
DECLARE lclCREATE_PGM CHAR (8);
DECLARE lclUPDATE_DT TIMESTAMP;
DECLARE lclUPDATE_USER CHAR (8);
DECLARE lclUPDATE_PGM CHAR (8);
-- Declare cursor to return updated record to client
DECLARE DB2_SQL1 CURSOR WITH RETURN FOR
SELECT
VUSERTSKLIST.USER_TSK_ID AS "UserTaskID",
VUSERTSKLIST.SYS_TSK_ID AS "SysTaskID",
VUSERTSKLIST.TSK_SUBJ_TXT AS "SubjectText",
VUSERTSKLIST.TSK_PRIOR_ID AS "PriorityID",
VUSERTSKLIST.TSK_STAT_ID AS "TaskStatusID",
VUSERTSKLIST.TSK_TYP_ID AS "TaskTypeID",
VUSERTSKLIST.CUR_TSK_STAT_IND AS "CurrentStatusInd",
VUSERTSKLIST.TSK_CLS_DT AS "TaskCloseDate",
VUSERTSKLIST.DTL_LNK_TXT AS "DetailLinkText",
VUSERTSKLIST.TBL_COL_NM AS "TableColumnName",
VUSERTSKLIST.TBL_COL_DAT_ID AS "TableColumnDataID",
VUSERTSKLIST.OPN_SCRN_ID AS "OpenCallObjectAdr",
VUSERTSKLIST.CMNT_TXT AS "CommentText",
VUSERTSKLIST.TSK_DUE_DT AS "TaskDueDate",
VUSERTSKLIST.ASGN_USER_ID AS "AsgnUserID",
VUSERTSKLIST.TSK_ASGN_DT AS "TaskAsgnDate",
VUSERTSKLIST.NXT_TARG_DT AS "NextTargetDate",
VUSERTSKLIST.TSK_CMPL_DT AS "TaskCmplDate",
VUSERTSKLIST.RQST_EXT_IND AS "RqstExtensionInd",
VUSERTSKLIST.RQST_EXT_DT AS "RqstExtensionDate",
VUSERTSKLIST.EXT_APRV_ID AS "ExtensionAprvID",
VUSERTSKLIST.EXT_APRV_DT AS "ExtensionAprvDate",
VUSERTSKLIST.RQST_FWRD_IND AS "RqstForwardInd",
VUSERTSKLIST.RQST_CLS_IND AS "RqstCloseInd",
VUSERTSKLIST.RQST_USER_TSK_ID AS "RqstUserTaskID",
VUSERTSKLIST.CREATE_DT AS "CreateDate",
VUSERTSKLIST.CREATE_USER AS "CreateUser",
VUSERTSKLIST.CREATE_PGM AS "CreateProgram",
VUSERTSKLIST.UPDATE_DT AS "UpdateDate",
VUSERTSKLIST.UPDATE_USER AS "UpdateUser",
VUSERTSKLIST.UPDATE_PGM AS "UpdateProgram"
FROM CC000.VUSER_TSK_LIST AS VUSERTSKLIST
WHERE
USER_TSK_ID=lclUSER_TSK_ID;
-- *****END DECLARATIONS*****
-- Set local variables to parameter values
SET lclUSER_TSK_ID=UserTaskID;
SET lclSYS_TSK_ID=SysTaskID;
SET lclTSK_SUBJ_TXT=SubjectText;
SET lclTSK_PRIOR_ID=PriorityID;
SET lclTSK_STAT_ID=TaskStatusID;
SET lclTSK_TYP_ID=TaskTypeID;
SET lvCUR_TSK_STAT_IND=CurrentStatusInd;
SET lclTSK_CLS_DT=TaskCloseDate;
SET lclDTL_LNK_TXT=DetailLinkText;
SET lclTBL_COL_NM=TableColumnName;
SET lclTBL_COL_DAT_ID=TableColumnDataID;
SET lclOPN_SCRN_ID=OpenCallObjectAdr;
SET lclCMNT_TXT=CommentText;
SET lclTSK_DUE_DT=TaskDueDate;
SET lclASGN_USER_ID=AsgnUserID;
SET lclTSK_ASGN_DT=TaskAsgnDate;
SET lclNXT_TARG_DT=NextTargetDate;
SET lclTSK_CMPL_DT=TaskCmplDate;
SET lclRQST_EXT_IND=RqstExtensionInd;
SET lclRQST_EXT_DT=RqstExtensionDate;
SET lclEXT_APRV_ID=ExtensionAprvID;
SET lclEXT_APRV_DT=ExtensionAprvDate;
SET lclRQST_FWRD_IND=RqstForwardInd;
SET lclRQST_CLS_IND=RqstCloseInd;
SET lvRQST_USER_TSK_ID=RqstUserTaskID;
SET lclCREATE_DT=CreateDate;
SET lclCREATE_USER=CreateUser;
SET lclCREATE_PGM=CreateProgram;
SET lclUPDATE_DT=UpdateDate;
SET lclUPDATE_USER=UpdateUser;
SET lclUPDATE_PGM=UpdateProgram;
-- test for delete, if delete is selected, delete record
--IF DELETE='y' or delete='Y' THEN
-- DELETE FROM CC000.VUSER_TSK_LIST
-- WHERE
-- USER_TSK_ID=lclUSER_TSK_ID;
-- test for exists, if does, then update, else insert
--ELSEIF EXISTS
IF EXISTS
(
SELECT USER_TSK_ID
FROM CC000.VUSER_TSK_LIST
WHERE
USER_TSK_ID=lclUSER_TSK_ID
) THEN
-- **TODO** (#5)
-- Update SQL statement for history table
--
UPDATE CC000.VUSER_TSK_LIST SET
SYS_TSK_ID=lclSYS_TSK_ID,
TSK_SUBJ_TXT=lclTSK_SUBJ_TXT,
TSK_PRIOR_ID=lclTSK_PRIOR_ID,
TSK_STAT_ID=lclTSK_STAT_ID,
TSK_TYP_ID=lclTSK_TYP_ID,
CUR_TSK_STAT_IND=lvCUR_TSK_STAT_IND,
TSK_CLS_DT=lclTSK_CLS_DT,
DTL_LNK_TXT=lclDTL_LNK_TXT,
TBL_COL_NM=lclTBL_COL_NM,
TBL_COL_DAT_ID=lclTBL_COL_DAT_ID,
OPN_SCRN_ID=lclOPN_SCRN_ID,
CMNT_TXT=lclCMNT_TXT,
TSK_DUE_DT=lclTSK_DUE_DT,
ASGN_USER_ID=lclASGN_USER_ID,
TSK_ASGN_DT=lclTSK_ASGN_DT,
NXT_TARG_DT=lclNXT_TARG_DT,
TSK_CMPL_DT=lclTSK_CMPL_DT,
RQST_EXT_IND=lclRQST_EXT_IND,
RQST_EXT_DT=lclRQST_EXT_DT,
EXT_APRV_ID=lclEXT_APRV_ID,
EXT_APRV_DT=lclEXT_APRV_DT,
RQST_FWRD_IND=lclRQST_FWRD_IND,
RQST_CLS_IND=lclRQST_CLS_IND,
RQST_USER_TSK_ID=lvRQST_USER_TSK_ID,
--CREATE_DT=lclCREATE_DT,
--CREATE_USER=lclCREATE_USER,
--CREATE_PGM=lclCREATE_PGM,
UPDATE_DT=lclUPDATE_DT,
UPDATE_USER=lclUPDATE_USER,
UPDATE_PGM=lclUPDATE_PGM
WHERE
USER_TSK_ID=lclUSER_TSK_ID;
ELSE
INSERT INTO CC000.VUSER_TSK_LIST(SYS_TSK_ID, TSK_SUBJ_TXT, TSK_PRIOR_ID, TSK_STAT_ID, TSK_TYP_ID, CUR_TSK_STAT_IND, TSK_CLS_DT, DTL_LNK_TXT, TBL_COL_NM, TBL_COL_DAT_ID, OPN_SCRN_ID, CMNT_TXT, TSK_DUE_DT, ASGN_USER_ID, TSK_ASGN_DT, NXT_TARG_DT, TSK_CMPL_DT, RQST_EXT_IND, RQST_EXT_DT, EXT_APRV_ID, EXT_APRV_DT, RQST_FWRD_IND, RQST_CLS_IND, RQST_USER_TSK_ID, CREATE_DT, CREATE_USER, CREATE_PGM, UPDATE_DT, UPDATE_USER, UPDATE_PGM)
VALUES(lclSYS_TSK_ID, lclTSK_SUBJ_TXT, lclTSK_PRIOR_ID, lclTSK_STAT_ID, lclTSK_TYP_ID, lvCUR_TSK_STAT_IND, lclTSK_CLS_DT, lclDTL_LNK_TXT, lclTBL_COL_NM, lclTBL_COL_DAT_ID, lclOPN_SCRN_ID, lclCMNT_TXT, lclTSK_DUE_DT, lclASGN_USER_ID, lclTSK_ASGN_DT, lclNXT_TARG_DT, lclTSK_CMPL_DT, lclRQST_EXT_IND, lclRQST_EXT_DT, lclEXT_APRV_ID, lclEXT_APRV_DT, lclRQST_FWRD_IND, lclRQST_CLS_IND, lvRQST_USER_TSK_ID, CURRENT TIMESTAMP, lclCREATE_USER, lclCREATE_PGM, CURRENT TIMESTAMP, lclUPDATE_USER, lclUPDATE_PGM);
SET lclUSER_TSK_ID = IDENTITY_VAL_LOCAL();
END IF;
-- Cursor left open for client application.
OPEN DB2_SQL1;
END P1