Maximo

Maximo

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

 View Only

  • 1.  Concise way to query for status synonyms?

    Posted Sat June 12, 2021 10:00 PM
    Edited by System Admin Wed March 22, 2023 11:47 AM
    I came across an blog post called Implicit Variable of Domain Synonym List in Relationship Where Clause - SQL

    I think that article is suggesting that there is a more concise way of querying for status synonyms than using a subquery on the SYNONYMDOMAIN table.

    The blog post is fairly light on the details.
    I tried putting this into the WOTRACK WHERE clause: (but got a generic SQL error)
    status = :&synonymlist&_wostatus[CLOSE,CAN]


    What am I doing wrong? Do I need to do something with a relationship?

    Thanks.


    #AssetandFacilitiesManagement
    #Maximo


  • 2.  RE: Concise way to query for status synonyms?

    Posted Mon June 14, 2021 07:46 AM
    Edited by System Admin Wed March 22, 2023 11:55 AM
    Hi.

    Try with this:

    status in (:&synonymlist&_wostatus[CLOSE,CAN])​

    If it doesn't work, please share the SQL error with us.


    ------------------------------
    Daniel Torrescusa 
    Piensa en nube, SL
    ------------------------------

    Original Message


  • 3.  RE: Concise way to query for status synonyms?

    Posted Mon June 14, 2021 08:33 AM
    Good catch. That works. Thanks.
    Original Message


  • 4.  RE: Concise way to query for status synonyms?

    Posted Mon June 14, 2021 09:51 AM

    1. It sounds like this method will be faster than using a subselect. So that's interesting.

    Special bind variables you can use
    :&synonymlist&_

    "This keyword is in lowercase. This resolves to a list of synonym values that can be directly used in a SQL. Following the &synonymlist&_ keyword, you specify the synonym domain name and a list of internal values. :&synonymlist&_DOMAINNAME[VALUE1,VALUE2,...ETC] will give you a string of ('synonymofvalue1', 'synonymofvalue2'...etc). This is very much recommended to be used in relationship where clause when you need to qualify the records by the synonym values of a synonym domain. The reason is that the resulted query will offer much better performance comparing to a subselect to synonym domain table. "


    2. I imagine it's safest to use internal values rather than the literal WO status values from the WORKORDER table.
    Reasons:
       - It's easy to forget about synonym values that aren't used often.
       - New synonym values might be added after the query is written, so the query owner might not think to update the query with the new synonym value. Using the internal value solves that issue.


    Does that sound right?


    Original Message


  • 5.  RE: Concise way to query for status synonyms?

    Posted Mon June 14, 2021 09:54 AM
    Edited by System Admin Wed March 22, 2023 11:46 AM
    Related:
    How to correctly handle synonym domains in automation script



    #Maximo
    #AssetandFacilitiesManagement


  • 6.  RE: Concise way to query for status synonyms?

    Posted Mon June 14, 2021 04:50 PM

    That doesn't surprise me.  A lot of those tricks only work in the Query by Example spaces.  This one is primarily for use in Conditional Expressions, Relationships and other things that translate before they execute.  If you try it in the "Status" field it will translate, but effectively it's just doing your 

    status = (select value from maximo.synonymdomain where domainid = 'WOSTATUS' and maxvalue in ('CAN','CLOSE')).

    Of course, in this case, you can just use historyflag = 1.



    ------------------------------
    Scott Mahon
    ------------------------------

    Original Message


  • 7.  RE: Concise way to query for status synonyms?

    Posted Mon June 14, 2021 04:51 PM
    Hi,

    :&synonymlist&_wostatus[CLOSE,CAN]

    is equivalent to

    SELECT VALUE FROM SYNONYMDOMAIN WHERE DOMAINID = 'WOSTATUS' AND MAXVALUE IN ( 'APPR')

    so it should work:

    status in (:&synonymlist&_wostatus[CLOSE,CAN])


    ------------------------------
    Pawel Nowicki
    ------------------------------

    Original Message


  • 8.  RE: Concise way to query for status synonyms?

    Posted Mon June 14, 2021 04:56 PM
    Please try:

    status in ( :&synonymlist&_wostatus[CLOSE,CAN] )


    ------------------------------
    Pawel Nowicki
    ------------------------------

    Original Message