Object structure query

When retrieving data, the REST API framework provides multiple ways to filter the data that is returned. When the existing options are insufficient, you can use an object structure query automation script.

For example, if you want to retrieve all work orders in an INPRG status that reference a particular ASSET or LOCATION you can generate the following SQL statement:

historyflag=0 and istask=0 and status='INPRG' and (location='BR430' or assetnum='11430')

The mboset implicit variable is the MboSet for the main object of your object structure. You use this variable to set the where clause that gets applied.

In the Automation Scripts application, click Create > Script for Integration. Click Object Structure and then select the object structure. Click Query Clause and then enter a unique query name.

In the Variables table, click New Row to add variables to the script that you want to be provided as query parameters (sqp:variable). These variables need to be defined as literal variables. If the query parameter is not provided in the URL in the REST call, the literal value that is defined in the script is used.

Create the query definition for the script. In the Object Structures application, add the query script type, select the script name, and provide a description. In the Object Structures application, go to the wanted object structure, for example, mxapiwo. Under Available Queries, select Query Definition. Select New Row. For Query Type, select script. The following example script sets a filter for the MboSet.

def isSet(variable):
    return variable and variable!='*'

from psdi.mbo import SqlFormat
query="historyflag=0 and istask=0 "
if isSet(assetnum) and isSet(location):
    query+=" and (assetnum=:1 or location=:2) "
elif isSet(assetnum):
    query+=" and assetnum=:1 "
elif isSet(location):
    query+=" and location=:2 "
if isSet(status):
    query+= " and status=:3 "
sqf=SqlFormat(query)

# Check that values are set before calling setObject or it fails
if isSet(assetnum):
    sqf.setObject(1,"WORKORDER","ASSETNUM",assetnum)
if isSet(location):
    sqf.setObject(2,"WORKORDER","LOCATION",location)
if isSet(status):
    sqf.setObject(3,"WORKORDER","STATUS",status)

mboset.setWhere(sqf.format())

Call the script with a REST API like the following example:

GET https://myurl.com/maximo/oslc/os/mxapiwodetail?lean=1&oslc.select=wonum,
description,siteid&oslc.pageSize=40&savedQuery=OSQUERY.MXAPIWODETAIL.EMXWOFILTER&sqp:
assetnum=11430&sqp:location=BR430