I am trying to create a stored procedure using SQL/400. I need to compare a date that I have converted to a character using the character function: CHAR(YEST2,ISO) (character version of yesterdays date) to an AS400 Date defined as DEC(8,0) also converted to Character. My reference book says that I should be able to use the CHAR function to convert my decimal date to character, but when I try to do that I get a 'MSGSQL0312' error. I have also tried defining my AS400 date variable as NUMERIC and then trying to use the CHAR function. I still get the same error when I try to compile the SQL procedure using the RUNSQLSTM command. My SQL procedure is listed below.
Please help. Thank you!!
CREATE PROCEDURE NAME_CHG5
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE YEST CHAR(8);
DECLARE HOSP DEC(3,0);
DECLARE SPID CHAR(20);
DECLARE PREVLN CHAR(35);
DECLARE NEWLN CHAR(35);
DECLARE PREVFN CHAR(35);
DECLARE PREVMN CHAR(35);
DECLARE NEWFN CHAR(35);
DECLARE NEWMN CHAR(35);
DECLARE CHGDATE NUMERIC(8,0);
DECLARE CCHGDATE CHAR(8);
DECLARE CHGBY CHAR(4);
DECLARE YEST2 DATE;
DECLARE CPNNMDT NUMERIC(10);
DECLARE DYEST2 DEC(8,0);
CREATE PROCEDURE NAME_CHG5
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE YEST CHAR(8);
DECLARE HOSP DEC(3,0);
DECLARE SPID CHAR(20);
DECLARE PREVLN CHAR(35);
DECLARE NEWLN CHAR(35);
DECLARE PREVFN CHAR(35);
DECLARE PREVMN CHAR(35);
DECLARE NEWFN CHAR(35);
DECLARE NEWMN CHAR(35);
DECLARE CHGDATE NUMERIC(8,0);
DECLARE CCHGDATE CHAR(8);
DECLARE CHGBY CHAR(4);
DECLARE YEST2 DATE;
DECLARE CPNNMDT NUMERIC(10);
DECLARE DYEST2 DEC(8,0);
DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE EOF_COND CHAR(1) DEFAULT '0';
DECLARE CSR1 CURSOR FOR
SELECT PNHSP, PNSPID, PNLSTN,
PNFSTN, PNMIDN, PELSTN,
PEFSTN, PNNMDT, PNLMBY
FROM BADMPNU, BADMPEU
WHERE PNHSP = PEHSP AND PNSPID = PESPID AND PNNMDT <> 0
AND CPNNMDT = DYEST2;
DECLARE CONTINUE HANDLER FOR NOT_FOUND SET EOF_COND = '1';
SET CPNNMDT = CHAR(PNNMDT);
SET YEST2 = CURDATE() - 1 DAY;
SET DYEST2 = CHAR(YEST2,ISO);
DELETE FROM NAMECHG;
OPEN CSR1;
RPT: REPEAT
FETCH CSR1
INTO HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN, NEWFN, NEWMN,
CHGDATE, CHGBY;
INSERT INTO NAMECHG VALUES(HOSP, SPID, PREVLN, PREVFN, PREVMN, NEWLN, NEWFN,
NEWMN, CHGDATE, CHGBY);
UNTIL EOF_COND = '1'
END REPEAT RPT;
CLOSE CSR1;
END;
Start Free Trial
