Maximo

 View Only
Expand all | Collapse all

Calculated list view field without applying db config changes

  • 1.  Calculated list view field without applying db config changes

    Posted Wed August 11, 2021 05:51 PM
    Edited by User1971 Thu August 12, 2021 03:53 PM
    MAM 7.6.1.2; Oracle 19c:

    I often need to create calculated fields in Maximo. Here's a specific example, although there are lots of different scenarios where this comes up.

    Classifications application:
    Add a USEWITH field to the list view that is a concatenated list of USEWITH values:



    Question:
    Is there a way to add a calculated field like that to the list view --  without the overhead of applying Database Configuration changes?

    -----------------------------------------------------------

    I ask because the options I'm aware of aren't really working out for me:

    1. Calculated SQL field (would be the ideal solution if it were easier to implement):
      1. Create a db view. Requires DBA involvement.
      2. Create a new object in Database Configuration based on the view. And create a relationship from CLASSSTRUCTURE to the new object/view.
      3. Apply database configuration changes. Requires involvement from our WebSphere admin due to clustered environments.
      4. Deploy the changes to each of our 6+ environments.
      5. Barrier: Doing a/c/d above would be a significant ordeal, requiring effort from multiple people. It's so much work that it's often not worth doing.
    2. Maximo attribute formula/custom field:
      1. I don't think there are any formula functions that can do what I want (group-by / concatenate a list). Although, I suppose an automation script could be used in conjunction with the formula for this.
      2. While it is easy to create an attribute formula (and an automation script), we would still need to create a persistent or non-persistent field via Database Configuration.
      3. Barrier: Same as above.
    3. Automation Script with launch point/custom field:
      1. Barrier: Same as above.


    ​I'm really hoping there is a way to add new calculated fields to applications -- without needing to be a DBA/WebSphere admin. I don't think I understand why we need to bring down the whole system just to calculate a simple field in the UI. 

    Any ideas? Thanks.



  • 2.  RE: Calculated list view field without applying db config changes

    Posted Wed August 11, 2021 05:59 PM
    Edited by User1971 Thu August 12, 2021 03:46 PM
    For what it's worth, this is how I'd do it with SQL, if calculated SQL fields were easier to implement:

    Create a full-blown SQL view/query:
    select 
        cl.classstructureid,
        cl.description,
        uw.usewith
    from 
        maximo.classstructure cl
    left join
        (
        select 
            classstructureid, 
            listagg(objectname,', ') within group(order by objectname) as usewith
        from 
            maximo.classusewith
        group by 
            classstructureid
        ) uw
        on cl.classstructureid = uw.classstructureid


    Or, if there were a way in Maximo to generate a specific attribute via a subquery, then it could be done like this:

    select 
        cl.classstructureid,
        cl.description,
        (select 
            listagg(objectname,', ') within group(order by objectname) as usewith
        from 
            maximo.classusewith
        group by 
            classstructureid
        having 
            classstructureid = cl.classstructureid
        ) as usewith
    from 
        maximo.classstructure cl
    

    Of course, the subquery method is a lot slower than a proper join.