Thank you very much for your help.
I was able to create a trigger which handilng any dates entered on the
screen. Just replaced select with update.
Here is part of the trigger for dates edit:
--#SET TERMINATOR !
CREATE TRIGGER EMPSCREDIT
AFTER INSERT ON EMP_SCREEN_EDIT
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE reason VARCHAR(70);
DECLARE OUT_SQLCODE1 INTEGER;
DECLARE OUT_SQLCODE2 INTEGER;
CALL execute_immediate('UPDATE EMP_SCREEN_EDIT SET HIREDATE =
hiredate
where CHECK_DATE(replace(hiredate,''-'','''')) IN
(''DATE
IS VALID'')',OUT_SQLCODE1);
CALL execute_immediate('UPDATE EMP_SCREEN_EDIT SET BIRTHDATE =
BIRTHDATE
where CHECK_DATE(replace(BIRTHDATE,''-'','''')) IN
(''DATE
IS VALID'')',OUT_SQLCODE2);
SET reason =
CASE WHEN OUT_SQLCODE1 = 100
THEN '292 INVALID HIREDATE'
WHEN OUT_SQLCODE2 = 100
THEN '343 INVALID BIRTHDATE'
CHECJ_DATE IS UDF FOR DATE EDIT IN FORMAT 'YYYYMMDD'
AND IT IS RETURNIG MESSAGE INVALID DATE OR VALID DATE.
Thank you Tonkuma for this UDF.
Serge Rielau wrote:
>db2 => ? SQL0084;
>
>SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or VALUES
> statement.
>
>Explanation:
>
>A SELECT or VALUES statement was used in an EXECUTE IMMEDIATE statement.
>
>The statement cannot be processed.
>
>User response:
>
>The implied function is not sup****ted. Prepare the SELECT or VALUES
>statement. Then use OPEN, FETCH, and CLOSE.
>
> sqlcode: -84
>
> sqlstate: 42612
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200807/1
|
