Maximo Open Forum

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Hi,

Perhaps I'm oversimplifying your questions, but wouldn't the Maximo where clause be:

 wonum in ( '624671','624672','624673','624674') and siteid = 'LEAD'

Please let me know if I've misunderstood your intent.

Stevie

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Hi Stevie,

Thank you for replying.  I appreciate your help.

Yes, the Maximo where clause would be wonum in('numbers') and siteid = 'LEAD' however, it would not capture any calculations.  Working out of 7.6.2. still and my objective is to create a Result Set lisitng status', ('WAPPR','INPRG','ON HOLD','SCHED','WMATL','MATRCVD','COMP','CLOSE').  Rather the WONUM in('numbers') and siteid = 'LEAD', it will probably go by work group so the customer won't have to look at a thousand+ work orders.  The issue I have is this is the only select statement that works and arranging the majority of the select into a where clause (and capturing all calculations) has been quite challenging. 

Basically, how can I arrange the entire select into a where? 

Thanks,

Adam

Hi,

Perhaps I'm oversimplifying your questions, but wouldn't the Maximo where clause be:

 wonum in ( '624671','624672','624673','624674') and siteid = 'LEAD'

Please let me know if I've misunderstood your intent.

Stevie

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Hi Adam,

If I correctly understand your requirement, you need a where clause to create a result set query. Then the result set would produce the numbers of work orders by status for a particular group. Is that right?

Hi Stevie,

Thank you for replying.  I appreciate your help.

Yes, the Maximo where clause would be wonum in('numbers') and siteid = 'LEAD' however, it would not capture any calculations.  Working out of 7.6.2. still and my objective is to create a Result Set lisitng status', ('WAPPR','INPRG','ON HOLD','SCHED','WMATL','MATRCVD','COMP','CLOSE').  Rather the WONUM in('numbers') and siteid = 'LEAD', it will probably go by work group so the customer won't have to look at a thousand+ work orders.  The issue I have is this is the only select statement that works and arranging the majority of the select into a where clause (and capturing all calculations) has been quite challenging. 

Basically, how can I arrange the entire select into a where? 

Thanks,

Adam

Hi,

Perhaps I'm oversimplifying your questions, but wouldn't the Maximo where clause be:

 wonum in ( '624671','624672','624673','624674') and siteid = 'LEAD'

Please let me know if I've misunderstood your intent.

Stevie

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Stevie,

Yes sir.  And, the number of days each status was in until the next status change.  This doesn't work as I am still working it but so far I have a where that needs a few things but similarily I would like to have something like this:

wonum in (select SITEID, ORGID, WONUM, CALNUM, STATUS
       to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,
       to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,
       ASSET_UTILITIES.CALENDAR_TIME_HOURS
          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE)) STATUS_HOURS
from (
      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS,  S.WOSTATUSID, S.CHANGEDATE, 
                lead(S.CHANGEDATE) over (order by S.WONUM, S.SITEID, S.CHANGEDATE) NEXT_CHANGEDATE
      from WORKORDER W
         left join WOSTATUS S
        on W.WONUM =:S.WONUM
        and W.SITEID =:S.SITEID
        ) A
and NEXT_CHANGEDATE is not null
order by WONUM, SITEID, CHANGEDATE ASC

Adam

Hi Adam,

If I correctly understand your requirement, you need a where clause to create a result set query. Then the result set would produce the numbers of work orders by status for a particular group. Is that right?

Hi Stevie,

Thank you for replying.  I appreciate your help.

Yes, the Maximo where clause would be wonum in('numbers') and siteid = 'LEAD' however, it would not capture any calculations.  Working out of 7.6.2. still and my objective is to create a Result Set lisitng status', ('WAPPR','INPRG','ON HOLD','SCHED','WMATL','MATRCVD','COMP','CLOSE').  Rather the WONUM in('numbers') and siteid = 'LEAD', it will probably go by work group so the customer won't have to look at a thousand+ work orders.  The issue I have is this is the only select statement that works and arranging the majority of the select into a where clause (and capturing all calculations) has been quite challenging. 

Basically, how can I arrange the entire select into a where? 

Thanks,

Adam

Hi,

Perhaps I'm oversimplifying your questions, but wouldn't the Maximo where clause be:

 wonum in ( '624671','624672','624673','624674') and siteid = 'LEAD'

Please let me know if I've misunderstood your intent.

Stevie

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Hi Adam,

While considering your needs, consider the info in this link.

https://bportaluri.com/2019/08/tracking-elapsed-time-in-work-order.html

Stevie

Stevie,

Yes sir.  And, the number of days each status was in until the next status change.  This doesn't work as I am still working it but so far I have a where that needs a few things but similarily I would like to have something like this:

wonum in (select SITEID, ORGID, WONUM, CALNUM, STATUS
       to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,
       to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,
       ASSET_UTILITIES.CALENDAR_TIME_HOURS
          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE)) STATUS_HOURS
from (
      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS,  S.WOSTATUSID, S.CHANGEDATE, 
                lead(S.CHANGEDATE) over (order by S.WONUM, S.SITEID, S.CHANGEDATE) NEXT_CHANGEDATE
      from WORKORDER W
         left join WOSTATUS S
        on W.WONUM =:S.WONUM
        and W.SITEID =:S.SITEID
        ) A
and NEXT_CHANGEDATE is not null
order by WONUM, SITEID, CHANGEDATE ASC

Adam

Hi Adam,

If I correctly understand your requirement, you need a where clause to create a result set query. Then the result set would produce the numbers of work orders by status for a particular group. Is that right?

Hi Stevie,

Thank you for replying.  I appreciate your help.

Yes, the Maximo where clause would be wonum in('numbers') and siteid = 'LEAD' however, it would not capture any calculations.  Working out of 7.6.2. still and my objective is to create a Result Set lisitng status', ('WAPPR','INPRG','ON HOLD','SCHED','WMATL','MATRCVD','COMP','CLOSE').  Rather the WONUM in('numbers') and siteid = 'LEAD', it will probably go by work group so the customer won't have to look at a thousand+ work orders.  The issue I have is this is the only select statement that works and arranging the majority of the select into a where clause (and capturing all calculations) has been quite challenging. 

Basically, how can I arrange the entire select into a where? 

Thanks,

Adam

Hi,

Perhaps I'm oversimplifying your questions, but wouldn't the Maximo where clause be:

 wonum in ( '624671','624672','624673','624674') and siteid = 'LEAD'

Please let me know if I've misunderstood your intent.

Stevie

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Hi Adam,

If I correctly understand your requirement, you need a where clause to create a result set query. Then the result set would produce the numbers of work orders by status for a particular group. Is that right?

Hi Stevie,

Thank you for replying.  I appreciate your help.

Yes, the Maximo where clause would be wonum in('numbers') and siteid = 'LEAD' however, it would not capture any calculations.  Working out of 7.6.2. still and my objective is to create a Result Set lisitng status', ('WAPPR','INPRG','ON HOLD','SCHED','WMATL','MATRCVD','COMP','CLOSE').  Rather the WONUM in('numbers') and siteid = 'LEAD', it will probably go by work group so the customer won't have to look at a thousand+ work orders.  The issue I have is this is the only select statement that works and arranging the majority of the select into a where clause (and capturing all calculations) has been quite challenging. 

Basically, how can I arrange the entire select into a where? 

Thanks,

Adam

Hi,

Perhaps I'm oversimplifying your questions, but wouldn't the Maximo where clause be:

 wonum in ( '624671','624672','624673','624674') and siteid = 'LEAD'

Please let me know if I've misunderstood your intent.

Stevie

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Hi Adam,

In Maximo, "where clause" is that part of SQL statement which is coming after "where".

The part before "where" is implied from the Maximo application used and the fields in the List view.

E.g.: in WOTRACK app, if I'm putting into Where clause the string:

siteid = 'BEDFORD' and status = 'COMP'

Maximo implies the following SQL statement:

select wonum, description, location, status, siteid [other-fields-List-View] from workorder where siteid = 'BEDFORD' and status = 'COMP'

Of course, you won't see "select wonum, description, location, status, siteid [other-fields-here] from workorder" part, but Maximo will consider it as such.

In the example that you presented there is basically no "where" part.

And it's not very clear what exactly you want to get with that selection.

I think that easier would be to define what you really want to get and to start from there.

Thank you,

Dragos

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Dragos,

A customer is wanting to track status' ('WAPPR','INPRG','ON HOLD','SCHEDULE','CLOSE','COMP') to look for bottlenecks in his org.  He'd like a Result Set that will list each status on the Result Set and the days in each status.  I'd like the Result Set to look similar to this:

With the select, we were able to list each work order by 'WOGROUP' (eliminating each work order number as there would be thousands).  Basically, I'm trying to pull the Work Order History data into a Result Set and all I have to work off of is the select and this select will capture the data we need in SQL.  Problem is, customers do not have access to SQL so a Result Set will have to do.  Hopefully you have a better understanding.

Thank you for replying,

Adam

Hi Adam,

In Maximo, "where clause" is that part of SQL statement which is coming after "where".

The part before "where" is implied from the Maximo application used and the fields in the List view.

E.g.: in WOTRACK app, if I'm putting into Where clause the string:

siteid = 'BEDFORD' and status = 'COMP'

Maximo implies the following SQL statement:

select wonum, description, location, status, siteid [other-fields-List-View] from workorder where siteid = 'BEDFORD' and status = 'COMP'

Of course, you won't see "select wonum, description, location, status, siteid [other-fields-here] from workorder" part, but Maximo will consider it as such.

In the example that you presented there is basically no "where" part.

And it's not very clear what exactly you want to get with that selection.

I think that easier would be to define what you really want to get and to start from there.

Thank you,

Dragos

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Dragos,

A customer is wanting to track status' ('WAPPR','INPRG','ON HOLD','SCHEDULE','CLOSE','COMP') to look for bottlenecks in his org.  He'd like a Result Set that will list each status on the Result Set and the days in each status.  I'd like the Result Set to look similar to this:

With the select, we were able to list each work order by 'WOGROUP' (eliminating each work order number as there would be thousands).  Basically, I'm trying to pull the Work Order History data into a Result Set and all I have to work off of is the select and this select will capture the data we need in SQL.  Problem is, customers do not have access to SQL so a Result Set will have to do.  Hopefully you have a better understanding.

Thank you for replying,

Adam

Hi Adam,

In Maximo, "where clause" is that part of SQL statement which is coming after "where".

The part before "where" is implied from the Maximo application used and the fields in the List view.

E.g.: in WOTRACK app, if I'm putting into Where clause the string:

siteid = 'BEDFORD' and status = 'COMP'

Maximo implies the following SQL statement:

select wonum, description, location, status, siteid [other-fields-List-View] from workorder where siteid = 'BEDFORD' and status = 'COMP'

Of course, you won't see "select wonum, description, location, status, siteid [other-fields-here] from workorder" part, but Maximo will consider it as such.

In the example that you presented there is basically no "where" part.

And it's not very clear what exactly you want to get with that selection.

I think that easier would be to define what you really want to get and to start from there.

Thank you,

Dragos

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

If I am reading this correctly...  Are you are looking for the number of days between work order status changes?

If so, then I have a very similar report... 

I use Work Order Status table to look at status changes / dates.

I use Lead/Lag function to calculate previous status / date to current status / dates.

LAG(WOSTATUS.CHANGEDATE) OVER ...  as Previous status/date

WOSTATUS.CHANGEDATE as current status/date

Current Status/date minus Previous (Lag formula)  status/date

However, this is in a Birt report, not in QBR or Maximo output.

Dragos,

A customer is wanting to track status' ('WAPPR','INPRG','ON HOLD','SCHEDULE','CLOSE','COMP') to look for bottlenecks in his org.  He'd like a Result Set that will list each status on the Result Set and the days in each status.  I'd like the Result Set to look similar to this:

With the select, we were able to list each work order by 'WOGROUP' (eliminating each work order number as there would be thousands).  Basically, I'm trying to pull the Work Order History data into a Result Set and all I have to work off of is the select and this select will capture the data we need in SQL.  Problem is, customers do not have access to SQL so a Result Set will have to do.  Hopefully you have a better understanding.

Thank you for replying,

Adam

Hi Adam,

In Maximo, "where clause" is that part of SQL statement which is coming after "where".

The part before "where" is implied from the Maximo application used and the fields in the List view.

E.g.: in WOTRACK app, if I'm putting into Where clause the string:

siteid = 'BEDFORD' and status = 'COMP'

Maximo implies the following SQL statement:

select wonum, description, location, status, siteid [other-fields-List-View] from workorder where siteid = 'BEDFORD' and status = 'COMP'

Of course, you won't see "select wonum, description, location, status, siteid [other-fields-here] from workorder" part, but Maximo will consider it as such.

In the example that you presented there is basically no "where" part.

And it's not very clear what exactly you want to get with that selection.

I think that easier would be to define what you really want to get and to start from there.

Thank you,

Dragos

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

If I am reading this correctly...  Are you are looking for the number of days between work order status changes?

If so, then I have a very similar report... 

I use Work Order Status table to look at status changes / dates.

I use Lead/Lag function to calculate previous status / date to current status / dates.

LAG(WOSTATUS.CHANGEDATE) OVER ...  as Previous status/date

WOSTATUS.CHANGEDATE as current status/date

Current Status/date minus Previous (Lag formula)  status/date

However, this is in a Birt report, not in QBR or Maximo output.

Dragos,

A customer is wanting to track status' ('WAPPR','INPRG','ON HOLD','SCHEDULE','CLOSE','COMP') to look for bottlenecks in his org.  He'd like a Result Set that will list each status on the Result Set and the days in each status.  I'd like the Result Set to look similar to this:

With the select, we were able to list each work order by 'WOGROUP' (eliminating each work order number as there would be thousands).  Basically, I'm trying to pull the Work Order History data into a Result Set and all I have to work off of is the select and this select will capture the data we need in SQL.  Problem is, customers do not have access to SQL so a Result Set will have to do.  Hopefully you have a better understanding.

Thank you for replying,

Adam

Hi Adam,

In Maximo, "where clause" is that part of SQL statement which is coming after "where".

The part before "where" is implied from the Maximo application used and the fields in the List view.

E.g.: in WOTRACK app, if I'm putting into Where clause the string:

siteid = 'BEDFORD' and status = 'COMP'

Maximo implies the following SQL statement:

select wonum, description, location, status, siteid [other-fields-List-View] from workorder where siteid = 'BEDFORD' and status = 'COMP'

Of course, you won't see "select wonum, description, location, status, siteid [other-fields-here] from workorder" part, but Maximo will consider it as such.

In the example that you presented there is basically no "where" part.

And it's not very clear what exactly you want to get with that selection.

I think that easier would be to define what you really want to get and to start from there.

Thank you,

Dragos

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

So QBR seems sufficient as I was able to create a where that will list each status of the work order.  Just can't seem to get the calculations to start.  But, so far; this is looking pretty good.

If I am reading this correctly...  Are you are looking for the number of days between work order status changes?

If so, then I have a very similar report... 

I use Work Order Status table to look at status changes / dates.

I use Lead/Lag function to calculate previous status / date to current status / dates.

LAG(WOSTATUS.CHANGEDATE) OVER ...  as Previous status/date

WOSTATUS.CHANGEDATE as current status/date

Current Status/date minus Previous (Lag formula)  status/date

However, this is in a Birt report, not in QBR or Maximo output.

Dragos,

A customer is wanting to track status' ('WAPPR','INPRG','ON HOLD','SCHEDULE','CLOSE','COMP') to look for bottlenecks in his org.  He'd like a Result Set that will list each status on the Result Set and the days in each status.  I'd like the Result Set to look similar to this:

With the select, we were able to list each work order by 'WOGROUP' (eliminating each work order number as there would be thousands).  Basically, I'm trying to pull the Work Order History data into a Result Set and all I have to work off of is the select and this select will capture the data we need in SQL.  Problem is, customers do not have access to SQL so a Result Set will have to do.  Hopefully you have a better understanding.

Thank you for replying,

Adam

Hi Adam,

In Maximo, "where clause" is that part of SQL statement which is coming after "where".

The part before "where" is implied from the Maximo application used and the fields in the List view.

E.g.: in WOTRACK app, if I'm putting into Where clause the string:

siteid = 'BEDFORD' and status = 'COMP'

Maximo implies the following SQL statement:

select wonum, description, location, status, siteid [other-fields-List-View] from workorder where siteid = 'BEDFORD' and status = 'COMP'

Of course, you won't see "select wonum, description, location, status, siteid [other-fields-here] from workorder" part, but Maximo will consider it as such.

In the example that you presented there is basically no "where" part.

And it's not very clear what exactly you want to get with that selection.

I think that easier would be to define what you really want to get and to start from there.

Thank you,

Dragos

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

I guess my next question would be, in QBR how would I calculate days between status date and changedate?  I can calculate MAXIMUM(statusdate) or MAX(changedate) but doesn't seem to count days between.  I used TIMESTAMPDATEDIFF as an expression however, it doesn't seem to work either.  

So QBR seems sufficient as I was able to create a where that will list each status of the work order.  Just can't seem to get the calculations to start.  But, so far; this is looking pretty good.

If I am reading this correctly...  Are you are looking for the number of days between work order status changes?

If so, then I have a very similar report... 

I use Work Order Status table to look at status changes / dates.

I use Lead/Lag function to calculate previous status / date to current status / dates.

LAG(WOSTATUS.CHANGEDATE) OVER ...  as Previous status/date

WOSTATUS.CHANGEDATE as current status/date

Current Status/date minus Previous (Lag formula)  status/date

However, this is in a Birt report, not in QBR or Maximo output.

Dragos,

A customer is wanting to track status' ('WAPPR','INPRG','ON HOLD','SCHEDULE','CLOSE','COMP') to look for bottlenecks in his org.  He'd like a Result Set that will list each status on the Result Set and the days in each status.  I'd like the Result Set to look similar to this:

With the select, we were able to list each work order by 'WOGROUP' (eliminating each work order number as there would be thousands).  Basically, I'm trying to pull the Work Order History data into a Result Set and all I have to work off of is the select and this select will capture the data we need in SQL.  Problem is, customers do not have access to SQL so a Result Set will have to do.  Hopefully you have a better understanding.

Thank you for replying,

Adam

Hi Adam,

In Maximo, "where clause" is that part of SQL statement which is coming after "where".

The part before "where" is implied from the Maximo application used and the fields in the List view.

E.g.: in WOTRACK app, if I'm putting into Where clause the string:

siteid = 'BEDFORD' and status = 'COMP'

Maximo implies the following SQL statement:

select wonum, description, location, status, siteid [other-fields-List-View] from workorder where siteid = 'BEDFORD' and status = 'COMP'

Of course, you won't see "select wonum, description, location, status, siteid [other-fields-here] from workorder" part, but Maximo will consider it as such.

In the example that you presented there is basically no "where" part.

And it's not very clear what exactly you want to get with that selection.

I think that easier would be to define what you really want to get and to start from there.

Thank you,

Dragos

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Hi Adam,

A little late to the party, but I had created a Chart Report counting the number of WO in each status. Did this using COUNT() then charted using a pie chart for results.  Simple, but maybe will trigger some ideas for you.

var whereTxt = new String();
whereTxt = "where " + params["where"];

whereTxt = whereTxt +" and workorder.status <> 'CLOSE' ";

var sqlText = new String();

// Add query to sqlText variable.
sqlText = "select status, count(1) as total_count from workorder "
// Include the Maximo where clause
    + whereTxt
     + " group by status order by status"
;

I guess my next question would be, in QBR how would I calculate days between status date and changedate?  I can calculate MAXIMUM(statusdate) or MAX(changedate) but doesn't seem to count days between.  I used TIMESTAMPDATEDIFF as an expression however, it doesn't seem to work either.  

So QBR seems sufficient as I was able to create a where that will list each status of the work order.  Just can't seem to get the calculations to start.  But, so far; this is looking pretty good.

If I am reading this correctly...  Are you are looking for the number of days between work order status changes?

If so, then I have a very similar report... 

I use Work Order Status table to look at status changes / dates.

I use Lead/Lag function to calculate previous status / date to current status / dates.

LAG(WOSTATUS.CHANGEDATE) OVER ...  as Previous status/date

WOSTATUS.CHANGEDATE as current status/date

Current Status/date minus Previous (Lag formula)  status/date

However, this is in a Birt report, not in QBR or Maximo output.

Dragos,

A customer is wanting to track status' ('WAPPR','INPRG','ON HOLD','SCHEDULE','CLOSE','COMP') to look for bottlenecks in his org.  He'd like a Result Set that will list each status on the Result Set and the days in each status.  I'd like the Result Set to look similar to this:

With the select, we were able to list each work order by 'WOGROUP' (eliminating each work order number as there would be thousands).  Basically, I'm trying to pull the Work Order History data into a Result Set and all I have to work off of is the select and this select will capture the data we need in SQL.  Problem is, customers do not have access to SQL so a Result Set will have to do.  Hopefully you have a better understanding.

Thank you for replying,

Adam

Hi Adam,

In Maximo, "where clause" is that part of SQL statement which is coming after "where".

The part before "where" is implied from the Maximo application used and the fields in the List view.

E.g.: in WOTRACK app, if I'm putting into Where clause the string:

siteid = 'BEDFORD' and status = 'COMP'

Maximo implies the following SQL statement:

select wonum, description, location, status, siteid [other-fields-List-View] from workorder where siteid = 'BEDFORD' and status = 'COMP'

Of course, you won't see "select wonum, description, location, status, siteid [other-fields-here] from workorder" part, but Maximo will consider it as such.

In the example that you presented there is basically no "where" part.

And it's not very clear what exactly you want to get with that selection.

I think that easier would be to define what you really want to get and to start from there.

Thank you,

Dragos

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam

Dragos,

A customer is wanting to track status' ('WAPPR','INPRG','ON HOLD','SCHEDULE','CLOSE','COMP') to look for bottlenecks in his org.  He'd like a Result Set that will list each status on the Result Set and the days in each status.  I'd like the Result Set to look similar to this:

With the select, we were able to list each work order by 'WOGROUP' (eliminating each work order number as there would be thousands).  Basically, I'm trying to pull the Work Order History data into a Result Set and all I have to work off of is the select and this select will capture the data we need in SQL.  Problem is, customers do not have access to SQL so a Result Set will have to do.  Hopefully you have a better understanding.

Thank you for replying,

Adam

Hi Adam,

In Maximo, "where clause" is that part of SQL statement which is coming after "where".

The part before "where" is implied from the Maximo application used and the fields in the List view.

E.g.: in WOTRACK app, if I'm putting into Where clause the string:

siteid = 'BEDFORD' and status = 'COMP'

Maximo implies the following SQL statement:

select wonum, description, location, status, siteid [other-fields-List-View] from workorder where siteid = 'BEDFORD' and status = 'COMP'

Of course, you won't see "select wonum, description, location, status, siteid [other-fields-here] from workorder" part, but Maximo will consider it as such.

In the example that you presented there is basically no "where" part.

And it's not very clear what exactly you want to get with that selection.

I think that easier would be to define what you really want to get and to start from there.

Thank you,

Dragos

Wondering if anyone can help me insert this select statement as a where clause.  I cannot seem to figure this one out.  The select statement works but I'm stumped as to how to modify the code into a where clause.  Problem is I do not know where to capture all of the select for the where....if that makes sense.  Here is the select.

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Thank you in advance.

Adam