Re: Bad Dates in Informix
Here is an example of the stored procedure that I use. You will see my
workaround (p_today) which, if just CURRENT used in its place, won't
work. You will see down within the FOREACH statement that I compare
dates. I believe that the p_assig_end_date should always have a valid
value, but I'll check to be sure it isn't null on occasion. Still, if
so, would that clear out the CURRENT date if I had CURRENT in place
of p_today?
-----------------------------------------------------------------------
CREATE PROCEDURE "informix".wo_triggers(p_branch CHAR(5), p_wo_num INT,
p_oldstatus CHAR(5), p_newstatus CHAR(5),
p_end_date DATE, p_flag CHAR(1) )
DEFINE p_assig_num INT;
DEFINE p_assig_end_date DATE;
DEFINE p_ssn CHAR(9);
DEFINE logtext CHAR(100);
DEFINE p_today DATE;
LET p_today = CURRENT;
-- Update assignment end_date to workorder end_date and
-- employee status to ZZ
-- if Workorder Status = "C", "X", or "L"
IF p_oldstatus != p_newstatus AND p_flag = 'U' THEN
IF p_newstatus = "C" OR p_newstatus = "X" or p_newstatus = "L" THEN
-- This will kick off the assignment trigger which should set the employee
-- status to it's correct value if it isn't already.
FOREACH
SELECT assig_num, end_date
INTO p_assig_num, p_assig_end_date
FROM as_fo_assignment
WHERE branch = p_branch AND wo_num = p_wo_num
IF p_assig_end_date > p_today THEN
UPDATE "informix".as_fo_assignment
SET end_date = p_today
WHERE branch = p_branch AND wo_num = p_wo_num
AND assig_num = p_assig_num;
END IF
END FOREACH
END IF
END IF
END PROCEDURE;