Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only

  • 1.  How do relationship conditions work? (underlying SQL)

    Posted Sun June 20, 2021 02:05 AM
    Edited by System Admin Wed March 22, 2023 11:48 AM

    MAM 7.6.1.2:

    I'm a data analyst by trade, so I tend to think in full-blown SELECT queries, joins, and group by. Whereas in Maximo we seem to deal more in subqueries and conditions.

    For example, Maximo relationships have conditions / WHERE clauses.






    At first, when I saw that WHERE Clause in the relationship, I pictured it as being the main WHERE clause in a full SELECT query on the entire CLASSSTRUCTURE table  / Classifications list view (not just a single record / the current record).

    select
    cl.classstructureid     as cl_classstructureid,
    cl.classificationid     as cl_classificationid,
    cl.description          as cl_description,
    anc.classancestorid     as anc_classancestorid,
    anc.ancestor            as anc_ancestor,
    anc.ancestorclassid     as anc_ancestorclassid,
    hierarchylevels         as anc_hierarchylevels
from
    maximo.classstructure cl
left join
    maximo.classancestor anc
    on cl.classstructureid = anc.classstructureid
where
    cl.classstructureid = anc.classstructureid
order by
    hierarchylevels desc
fetch
    first row only

    image.png
    --1 row selected from the entire CLASSSTRCUTRE table.


    But I don't think that's how it works.

    Instead, I'm wondering if it works like this:

    • For each individual record, Maximo creates a subquery that selects from the records in the related table, where the specified fields match: 

    select
    classstructureid,
    classificationid,
    description,
    (select ancestor from maximo.classancestor where classstructureid = cl.classstructureid and rownum = 1) 
            as anc_ancestor
from
    maximo.classstructure cl

    image.png
    --There were actually thousands of records that were returned from the query. But I applied a filter to get just the one record, for the purpose of this question.

    I added ROWNUM=1 to the subquery, since in the SELECT clause, SQL subqueries can only return a single record. I'm guessing Maximo might work this way too -- it seems to select an arbitrary CLASSANCESTOR record to join to the original CLASSSTRUCTURE record. Just like I did with ROWNUM=1.
    But I suppose it depends on the scenario; some places in Maximo select multiple related records, others just seem to select a single record.

    Question:
    Is that how relationship conditions work in Maximo? Or am I way off?

    I do seem to have a mental block when it comes to these conditions/subqueries.


    #Maximo
    #AssetandFacilitiesManagement


  • 2.  RE: How do relationship conditions work? (underlying SQL)

    Posted Mon June 21, 2021 09:18 AM
    Any time you have : syntax in a relationship (IE :wonum), Maximo is going to replace this with a literal value (IE '1001'). Going through a relationship will still function the same way, but will only return the first record returned from the relationship (if it's 1:M). It does this by grabbing the in-memory values in the MBO records and if there aren't any in-memory, Maximo will initialize the set to get the value. If you hadn't limited this to 1 record and someone had changed it to sort descending for example then the value returned could be different than another user who left it sort ascending. I rarely use an attribute through a relationship on relationships because of the 1 to many limitations. I write these as subselects instead to ensure I get all the records from that child object as it's very rare I'm just trying to look at 1 of them. 

    There are very few scenarios where Maximo does literal joins between objects (outside of reports of course, which do this almost all the time). In general, assume that the query being executed are SELECT * FROM object WHERE criteria. Joining back to the existing record causes more work for the database platform in that scenario and doesn't add value as Maximo doesn't need to retrieve anything from the original record anymore. 

    There are a few reasons why this is done. If you're executing the query up front and know everything you need to retrieve, joins are almost always preferred. If you're getting WO data, but know you need LOCATION & ASSET data, then joining in advance is almost always beneficial. But that's not how Maximo operates. Maximo objects retrieve related sets in the code as it's needed. Sometimes when you initialize WO you won't need LOCATION or ASSET data. Thus, Maximo retrieves these as needed. And there are scenarios where you need a non-persistent attribute in the relationship which isn't stored in the database (at least, not on the object you're on). So, Maximo uses substitution variables to replace these with the literal values.

    ------------------------------
    Steven Shull
    Director of Development
    Projetech Inc
    Cincinnati OH
    ------------------------------

    Original Message


  • 3.  RE: How do relationship conditions work? (underlying SQL)

    Posted Mon June 21, 2021 01:56 PM
    When developing relationships for Maximo, I open my SQL tool and write the following to simulate what Maximo is going to do:

    select * from CHILDOBJECT
    where 
    CRITERIA
    ;

    Notes:
    • If this was a relationship from WORKORDER to ASSET, CHILDOBJECT would be ASSET.
      • Aliases are not allowed. No "ASSET as A".
    • CRITERIA is my where clause -- the only part of the query I'm allowed to change
      • Values of attributes from the parent object, e.g. WORKORDER, are substituted in with :ATTRNAME (note the prefixed colon).
        • Some SQL tools will recognize variables like this in your query and let you supply literal values, which is handy for testing.
      • If you have subqueries in your criteria, you can alias those tables, but the CHILDOBJECT must always be unaliased.
        • So, if CHILDOBJECT is ASSET and you want to see if this asset isn't the parent of another, you could do something like this to make ASSET refer to the outer query's ASSET:
          • exists (select 1 from asset as A2 where parent = ASSET.assetnum and siteid = ASSET.siteid)
    • SQL Server users would replace the trailing semicolon with "GO".
    • Relationships allow ORDER BY clauses, but most places don't. So, "leave them out" is the general rule.
    • If the relationship is to be used for showing data in fields, make sure you get exactly 1 row back or that it is okay to be read-only and blank if no rows come back.
      • For 1:M use cases, like tables in the UI, you'll want test scenarios that return 0 and many rows.


    ------------------------------
    Blessings,
    Jason Uppenborn
    Sr. Technical Maximo Consultant
    Ontracks Consulting
    ------------------------------

    Original Message


  • 4.  RE: How do relationship conditions work? (underlying SQL)

    Posted Tue June 22, 2021 12:50 PM
    Edited by System Admin Wed March 22, 2023 11:46 AM
    Thanks Jason. For my purposes, I might dumb-down your sample query even further:

    For the current record (PARENTOBJECT): WO1000

Get the records where the WONUMs match:

--We can mock up a full query in an SQL tool...
select wonum from CHILDOBJECT
where 
CRITERIA -- ...but ultimately, this is the only part of the query that can be used in the condition.
;


    I think I'm starting to get it now. It's not complicated, it's just different that what I'm used to.


    #AssetandFacilitiesManagement
    #Maximo


Related Content