null DATE handler in informix

aneeshtd · #1 (**permalink**) 01-26-12, 23:57

Hi,

i have a stored procedure, in which i update a table based on date (inside a cursor). query seems like
update table1 set data1= "some value"
where date1 = T_date;

T_date is selected from the select query of the cursor. some times the T_date can be null and in that case am getting error
696: Variable (t_date) has undefined value.
Error in line 565
Near character position 43

is there any chance to handle this? please reply..

begooden-it · #2 (**permalink**) 01-27-12, 04:40

Hi,

a simple way would be to code as follows

Code:

IF T_date IS NOT NULL THEN
    update table1 set data1= "some value"
    where date1 = T_date; 
END IF;

Another way would be to handle the -696 exception with ON EXCEPTION
something like

Code:

ON EXCEPTION IN (-696) 
put here how you handle this error
END EXCEPTION

update table1 set data1= "some value"
where date1 = T_date;

If you want to debug all this, use :

Code:

SET DEBUG FILE TO 'filename';
TRACE ON;
more code here

You could also try to determine whether is is acceptable to have a null value for t_date, and set a relevant constraint on it if necessary.

Regards,
Eric

Internet Services

Web Development

Digital Marketing

Consumer Tech