Maximo Open Forum

 View Only

  • 1.  SQL Command

    Posted 02-14-2022 03:35
    Hi,

    Did anyone know how to extract those information with blue mark color in the photo by using SQL?



    I did try to extract the data from this command, but the result looks like not correct.

    SELECT WORKORDER.WONUM, WOACTIVITY.WONUM, WORKORDER.DESCRIPTION, WOACTIVITY.DESCRIPTION, WOACTIVITY.LOCATION,
    WOACTIVITY.ASSETNUM, WOACTIVITY.TASKID, WOACTIVITY.POINTNUM
    FROM WOACTIVITY, WORKORDER
    WHERE WOACTIVITY.WONUM = WORKORDER.WONUM
    AND WORKORDER.STATUS != 'CAN'
    AND WOACTIVITY.POINTNUM IS NOT NULL
    AND WORKORDER.JPNUM = '100105'




    #Administration
    #EverythingMaximo

    ------------------------------
    Nor Herman Afandi
    ------------------------------


  • 2.  RE: SQL Command

    Posted 02-14-2022 09:06

    You want something more like what I have below. 

    The join from WORKORDER->WOACTIVITY is based on the wonum on WORKORDER matching the parent field on WOACTIVITY. Because the section you highlighted will only show tasks, I also applied a filter to only include tasks. Maximo has a concept of sites which is where the same WO # (or Asset, Location, PO, etc.) might be used in multiple sites. It's a best practice to ensure you're including this in your joins even if you only have one site today to avoid issues down the road. 

    When it comes to joining tables, I'm a strong proponent of increased readability. While doing "from woactivity,workorder" and then building the join in the where clause is technically valid, it's typically more difficult to read. I also aliased the columns for wonum & description since you're retrieving the same column name from 2 different tables and you want to know which you're retrieving exactly (instead of something like WONUM & WONUM_1 if you're on Oracle).

    SELECT workorder.wonum as parentwo, woactivity.wonum as taskwo, workorder.description as parentdesc, woactivity.description as taskdesc, woactivity.location,
    woactivity.assetnum, woactivity.taskid, woactivity.pointnum
    FROM workorder
    INNER JOIN woactivity on workorder.wonum=woactivity.parent and workorder.siteid=woactivity.siteid and woactivity.istask=1
    WHERE workorder.status!='CAN'
    AND woactivity.pointnum IS NOT NULL
    AND workorder.jpnum = '100105';

    ​

    ------------------------------
    Steven Shull
    IBM
    ------------------------------

    Original Message


  • 3.  RE: SQL Command

    Posted 02-15-2022 22:49
    Hi Steven,

    Thank you for your help, explanation and example of SQL command that you specify. It works with expected result I want.
    Really appreciate it.

    ------------------------------
    Nor Herman Afandi
    ------------------------------

    Original Message


Related Content