GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

IF statement: Comparision for NULL

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • IF statement: Comparision for NULL

    I have a question on If statement while comparision with NULL value.

    I have a trigger on a table which fires before insert for each row. In that trigger I am setting the default values for inserted date, inserted by etc. All that I am doing is to check if the passed value is null and if it is null then set it to a default value. However ELSE part of the statement is being executed. Can some body help me to identify what the issue is.

    I am inserting a row thru PHPMyAdmin and below is the insert statement generated.

    INSERT INTO `generic`.`sequence_generator` (
    `unq_id` ,
    `tbl_nm` ,
    `max_value_plus_one` ,
    `effctv_dttm` ,
    `trmntd_dttm` ,
    `insrtd_by` ,
    `insrtd_dttm` ,
    `last_updtd_by` ,
    `last_updtd_dttm`
    )
    VALUES (
    '', 'mmm', '1', '', NULL , '', '', '', ''
    );

    The part that is not working. This is extracted from the trigger.

    if new.effctv_dttm is null
    or new.effctv_dttm = ''
    or length(new.effctv_dttm) = 0
    OR length(new.effctv_dttm) = NULL
    then
    set new.effctv_dttm = current_timestamp;
    else
    set new.effctv_dttm = '2000-01-01 00:00:00';
    end if;

  • #2
    Hi,

    Did you tried calling current_timestamp with () at the end? )

    Istvan

    Comment


    • #3
      correct syntax is 'IS NULL'.

      Comment


      • #4
        istvan.podor wrote on Sat, 08 May 2010 11:35

        Hi,

        Did you tried calling current_timestamp with () at the end? )

        Istvan

        I agree - this part is incorrect:
        OR length(new.effctv_dttm) = NULL

        Troy

        Comment

        Working...
        X