4

In Maximo 7.6.1.1:

I have an attribute formula on a persistent field called WORKORDER.X. The field type is decimal, length is 18, and scale is 10.

The formula is meant to do this:

  • If WOSERVICEADDRESS.LONGITUDEX is not null, use it
  • Else, if ASSET.X is not null, use it
  • Else, if LOCATION.X is not null, use it

This is the expression I've come up with:

NVL(SERVICEADDRESS$LongitudeX, NVL(ASSET$X, NVL(LOCATION$X,0) ) )
                                                           ^
                                           I would prefer null, not 0

Question:

If all of the search-values end up being null, is there a way to return null, rather than 0?

I've tried using the word null, but I get an error:

BMXAA9399E - This formula is invalid because the following attribute or token is 
             invalid: null. Specify a valid attribute and try again.

I've also tried "", but that returns 0.

And using a field where the value is null also throws an error.

Edit:

I have a related question here: Maximo formula that uses a custom formula function/automation script?

User1973
  • 108
  • 5
  • 27
  • What type of field is the X and the Y? Integer? Decimal? ALN? Some of those field types don't accept nulls. – Dex Dec 23 '19 at 03:28
  • @Dex Thanks for your input. The field type for X and Y is: `decimal, length 18, and scale 10`. When you say *"...some of those field types don't accept nulls"*, don't the fields start out as nulls in the db? If so, I don't think I understand why it wouldn't be possible to set them as null with a formula. Setting a field to null with an SQL insert statement is possible--regardless of the datatype. Is Maximo different somehow? – User1973 Dec 23 '19 at 03:53
  • It is a little different. For example, YORN fields should not be null. Maximo stores a YORN field as an integer in the database, but also puts a database-level default value on that field so it doesn't have nulls, and/or it runs an update statement to set the contents of the fields to zero, because its code doesn't expect to see nulls in those types of fields. Some database systems just don't allow an empty string in those fields. Oracle will treat an empty string in an integer column as null, but SQL Server treats it as a base value, in this case it treats it as zero. – Dex Dec 23 '19 at 16:39
  • @Dex In a [separate question](https://stackoverflow.com/questions/59888911/maximo-formula-that-uses-a-custom-formula-function-automation-script), I'm having an issue with `java.lang.NullPointerException`. I wonder if it is related to your comment above. I looked up NullPointerException and this is what I found: [What is a NullPointerException, and how do I fix it?](https://stackoverflow.com/a/218510/10936066). – User1973 Jan 24 '20 at 02:17
  • I take it `~null~` doesn't work? – Sun Apr 20 '20 at 14:44
  • @Sun Good idea. I haven't had a chance to try it. – User1973 Apr 20 '20 at 15:26

1 Answers1

2

I haven't played much with the formulas yet but they are used to calculate math values:

From the documentation:

The scope of Maximo formula's are limited to mathematical expressions and hence it can be only used to calculate numerical values.

Source: Maximo Formulas PDF

So you might be better to use an automation script if you really need to get a null value.

JPTremblay
  • 891
  • 5
  • 8
  • Yeah, you might be right. A second [pdf about formulas](https://www.ibm.com/developerworks/community/wikis/form/anonymous/api/wiki/02db2a84-fc66-4667-b760-54e495526ec1/page/03ad118c-6040-43dd-bc6d-d7a03510d135/attachment/37c2dd71-2dab-4371-a7b6-782fda617bf4/media/MaximoFormulasV2%20%281%29.pdf) says, `"The formula expression now supports handling ALN values directly inside the formula."` So I wondered if returning `null` would be doable. – User1973 Jan 06 '20 at 18:06
  • I think it's because in the first release you needed to define a specific condition for each ALN value that you wanted to use in a formula. See `Evaluating conditions` in the first document. – JPTremblay Jan 06 '20 at 18:15