7 Replies Latest reply: Jul 22, 2014 11:53 AM by Massimo Grossi RSS

    Different Results - NOT IsNull(Field)  vs len(Field) > 0

      Hi All,

       

      I am getting different results with NOT IsNull and Len functions

       

      DATA_TAB:

      load * inline

      [ID,VAL

      1,10-VAL

      2,20-VAL

      3,

      4,40-VAL

      5

      6,''];

       

      REF_TAB:

      load * inline

      [key, NAME,DT

      10-VAL, Value of 10, 1/12/2014

      10-VAL, Value of 10, 1/1/2010

      10-VAL, Value of 10, 12/12/2010

      20-VAL, Value of 20,2/1/2014

      20-VAL, Value of 20,10/1/2014

      20-VAL, Value of 20,

      ,EMPTY Value,9/9/2013];

       

       

      left join (DATA_TAB)

      load

          key as VAL,

          min(DT) as MIN_DT

      Resident

           REF_TAB

      where

      len(key) > 0 /*----- Working */

          //NOT isnull(key)  /*------ NOT Working */

      group by

          key

      ;

      Here are the results

      Using Len(key) > 0 , it does NOT load the nulls

      But while using NOT isnull(key) , it loads the nulls as well

       

       

      Does anyone know what could be wrong?

       

      Thanks,

      AP