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?

#Maximo
#AssetandFacilitiesManagement

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?



#Maximo
#AssetandFacilitiesManagement

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

Original Message:
Sent: Tue September 14, 2021 09:51 PM
From: User1971
Subject: How do formulas handle math on nulls?


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?



#Maximo
#AssetandFacilitiesManagement

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

Original Message:
Sent: Tue September 14, 2021 09:51 PM
From: User1971
Subject: How do formulas handle math on nulls?


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?



#AssetandFacilitiesManagement
#Maximo