Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Ontario, Canada
    Posts
    2

    Unanswered: Test for inequality not working .. why?

    Hello..

    I am trying to execute the following logic in a stored procedure: I want to insert a record into a table if a particular inbound parameter is NOT NULL and is not a 0-length string (i.e. ''). Seems pretty simple. Yet I am utterly confused as to why the following statements do not work:
    Code:
    IF (vr_Tracking_Id IS NOT NULL) THEN
        IF (vr_Tracking_Id != '') THEN
            INSERT INTO tracking(enrollmentid, agl_tracking_id)
            VALUES (vr_enroll_id, vr_Tracking_Id);
        END IF;
    END IF;
    ...and ....
    Code:
    IF (vr_Tracking_Id IS NOT NULL) AND (vr_Tracking_Id != '') THEN
        INSERT INTO tracking(enrollmentid, agl_tracking_id)
        VALUES (vr_enroll_id, vr_Tracking_Id);
    END IF;
    ... do not work.

    But if I remove test for inequality, as follows:
    Code:
    IF (vr_Tracking_Id  IS NOT NULL) THEN
        --IF (vr_Tracking_Id != '') THEN
            INSERT INTO tracking(enrollmentid, agl_tracking_id)
            VALUES (vr_enroll_id, vr_Tracking_Id);
        --END IF;
    END IF;
    ... it works fine!

    BTW, I also tried using <> instead of != and it didn't work. It does not like my second condition at all, the test for inequality. Any suggestions?

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    This happens because Oracle treats zero length strings as NULL, and the =, != operators do not work on them.

    This deviates from the SQL 92 standard.

    http://download-west.oracle.com/docs...mds.htm#126454

Posting Permissions

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