Maximo

Maximo

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

 View Only
Back to discussions
Expand all | Collapse all

Search for specific value in a relationship attribute in result set in maximo

  • 1.  Search for specific value in a relationship attribute in result set in maximo

    Posted Wed December 01, 2021 05:00 AM
    Hi Team,


    I have an requirement like this: A result set query that will identify the highest scoring Defect against an asset and its most recent respective deterioration.

    My Saved Query in Wotrack Application:

    wonum in(
    select distinct w.wonum from workorder w
    left join ticket t on t.assetnum=w.assetnum and t.STATUS IN ('INPROG','NEW','ONHOLD','PENDING','QUEUED')
    left join ticketspec ts on t.ticketid=ts.ticketid
    LEFT JOIN WFTRANSACTION wf ON wf.OWNERID=w.WORKORDERID
    left join worklog wl on wl.recordkey=t.ticketid
    where w.workorderid in(select ownerid from wfassignment where assignstatus='ACTIVE' and roleid='SEEG' and ownertable='WORKORDER') and
    (ts.numvalue in(select max(numvalue) from ticketspec where assetattrid='DFCT_RISK_SCR' and ticketid in(select ticketid from ticket where
    assetnum=w.assetnum and ticket.STATUS IN ('INPROG','NEW','ONHOLD','PENDING','QUEUED') ))
    and(w.status = 'COMPLETE' and (w.woclass = 'WORKORDER' or w.woclass = 'ACTIVITY') and w.historyflag = 0 and w.istask = 0 and w.siteid = 'CVL' and
    (w.jpnum like '%C00528%' or w.jpnum = 'LOW001' or w.jpnum = 'AK0041' or w.jpnum = 'C00530' or w.jpnum = 'C00529' or w.jpnum = 'AK0190' or w.jpnum = 'AK0189' or
    w.jpnum = 'C00546' or w.jpnum = 'C00547' or w.jpnum = 'AK6016' or w.jpnum = 'AK6015' or w.jpnum = 'AK6012' or w.jpnum = 'AK6011' or w.jpnum = 'AK6010' or w.jpnum = 'AK6009' or
    w.jpnum = 'AK6001' or w.jpnum = 'AK6002' or w.jpnum = 'AK6003' or w.jpnum = 'AK6004' or w.jpnum = 'AK6005' or w.jpnum = 'AK6006' or w.jpnum = 'AK0038' or w.jpnum = 'C00555' or
    w.jpnum = 'C00557' or w.jpnum = 'C00556'))
    and wf.transdate in(select max(transdate) from WFTRANSACTION where ownerid=w.WORKORDERID))
    and wl.CREATEDATE =(SELECT max(CREATEDATE) FROM WORKLOG where RECORDKEY=t.TICKETID)
    )

    Maximo Relationships in DB Config:

    Workorder(P) to Ticket(C) is 
    assetnum=:assetnum and ticket.status IN ('INPROG','NEW','ONHOLD','PENDING','QUEUED') and siteid = :siteid

    Ticket(P) to Ticketspec(C) is 
    ticketspec.ticketid in (select ticketid from ticket where CLASS='DEFECT' AND STATUS IN ('INPROG','NEW','ONHOLD','PENDING','QUEUED') AND
    assetnum =:ASSETNUM) and assetattrid='DFCT_RISK_SCR' and numvalue in(select max(numvalue) from ticketspec where assetattrid='DFCT_RISK_SCR' and ticketid in (select ticketid from ticket where
    CLASS='DEFECT' AND STATUS IN ('INPROG','NEW','ONHOLD','PENDING','QUEUED') AND assetnum =:ASSETNUM)) and siteid = :siteid



    Here My Problem is,  I am able to display correct data in my result set with highest defect risk score against an asset .But, if i search for a value say as 25 in defect score it is displaying data which consists of 9,6,15,10.. defect risk score also.I feel , need to make some changes in Relationships.

    Can anyone help me on this please.
    Thanks in Advance.

    Regards,
    Anuja.

    ------------------------------
    Anuja Dhanekula
    ------------------------------

    #AssetandFacilitiesManagement
    #Maximo


Related Content