Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: null DATE handler in informix

    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..

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •