Maximo

Expand all | Collapse all

How do formulas handle math on nulls?

  • 1.  How do formulas handle math on nulls?

    Posted 8 days ago
    Edited by User1971 8 days ago

    MAM 7.6.1.2:


    Background info:
    In SQL, we can't do math on nulls. Example: 1 + null = null. 
    This is where I make the majority of my SQL mistakes.
    I often assume, incorrectly, that nulls are treated as zeros: 1 + null = 1   [it doesn't work like that]

    Question:
    How do formulas handle math on nulls?
    Do they behave just like SQL?  1 + null = null  
    Or do formulas treat nulls as zeros when math is involved? Example: 1 + null = 1



    Edit:
    A related post here:
    Can formulas return null?


  • 2.  RE: How do formulas handle math on nulls?

    Posted 8 days ago
    Edited by User1971 8 days ago

    It looks like formulas behave similarly to SQL.


    We can't do math on nulls with formulas either:
    1 + nullvalue( ) = null  

    I suppose the next question is:
    What are some scenarios where there are nulls in formula expressions?
    For example, if we use SUM to sum up records, but there isn't anything to sum on in the field we're looking at, does SUM return null —or— 0 to the formula expression?




  • 3.  RE: How do formulas handle math on nulls?

    Posted 8 days ago
    Edited by Michael Kasteel 8 days ago
    for SQL check out the COALESCE function.  It allows SQL startements to replace null values with other values by evaluating thr options left to right until a !=NULL is returned.

    eg COALESCE([attribute that coudl be null],0) will return 0 if the attribute in that instance is NULL.  The attribute value would be returned if it is not null

    in you example you shoud be able to use 1 + CAOLESCE([whatever you are evaluatig that = null],0) = 1.  This assumes that the absesnce of data in your instance is assumed to be zero.  You can also do this with multiple attrubutes as it evaluates the attribute list from left to right until it gets a  !=NULL respose. This can also be used for alphanumerical as well as numeric values.    

    There's plenty of pages with info on this, here's the first one I found on google that looked useful
    https://www.sqltutorial.org/sql-comparison-functions/sql-coalesce/ 


    ------------------------------
    Michael Kasteel
    Director
    ISW
    0402830412
    ------------------------------



  • 4.  RE: How do formulas handle math on nulls?

    Posted 8 days ago
    I haven't used formulas often but I believe the SUM function will return 0. For example if you get the SUM of the linecost on POLINES and there are no POLINES I believe the value returned would be 0. Most attributes in Maximo that are numeric will default to a 0 so I can't think of a scenario off the top of my head where a value would be null but there probably are examples of where you are trying to perform math on two attributes on the same record (IE not using the SUM function) where you may add a 1 to a null value.

    There's an isnull function that allows you to write an IF statement and check the value before trying to use it your in equation.

    ------------------------------
    Steven Shull
    ------------------------------