This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
I'm trying to run the query below from a program called Golden but it's not pulling the correct numbers from Maximo 5.2.
Does anyone know what might be wrong with this?
select 'CREW 220 a SO PM total:',count(*)
from workorder WHERE (upper(crewid) = 'CREW 220' or upper(crewid) = 'CREW 21') and supervisor like 'OTTSTE'
and reportdate >= to_timestamp ('2010-12-31 23:00:00' , 'YYYY-MM-DD HH24:MI:SS.FF') and istask = 'N' and worktype = 'PM'
and (exists (select siteid from maximo.locancestor where ((ancestor like 'FORT%'))
and (location =workorder.location and systemid = ( select primarysystem from site
where siteid = workorder.siteid) and siteid = 'EEP')))
union
select 'CREW 220 b comp_close:',count(*)from workorder
WHERE status IN ('COMP','CLOSE') and (upper(crewid) = 'CREW 220' or upper(crewid) = 'CREW 21') and supervisor like 'OTTSTE%'
and reportdate >= to_timestamp ('2010-12-31 23:00:00' , 'YYYY-MM-DD HH24:MI:SS.FF') and istask = 'N' and worktype = 'PM'
and (exists (select siteid from maximo.locancestor where ((ancestor like 'FORT%'))
and (location =workorder.location and systemid = ( select primarysystem from site
where siteid = workorder.siteid) and siteid = 'EEP')))
As an approach to debugging, do the single query without the UNION but make STATUS the first field and do a Group By STATUS.
Of course remove current references to specific Statuses.
Then look at individual Status counts and assess.
Sent on the Sprint� Now Network from my BlackBerry�
-----Original Message-----
From: "Tima" <timawolff@yahoo.com>
Sender: MAXIMO@yahoogroups.com
Date: Wed, 27 Jul 2011 20:37:08
To: <MAXIMO@yahoogroups.com>
Reply-To: MAXIMO@yahoogroups.com
Subject: [MAXIMO List] Query
I'm trying to run the query below from a program called Golden but it's not pulling the correct numbers from Maximo 5.2.
Does anyone know what might be wrong with this?
select 'CREW 220 a SO PM total:',count(*)
from workorder WHERE (upper(crewid) = 'CREW 220' or upper(crewid) = 'CREW 21') and supervisor like 'OTTSTE'
and reportdate >= to_timestamp ('2010-12-31 23:00:00' , 'YYYY-MM-DD HH24:MI:SS.FF') and istask = 'N' and worktype = 'PM'
and (exists (select siteid from maximo.locancestor where ((ancestor like 'FORT%'))
and (location =workorder.location and systemid = ( select primarysystem from site
where siteid = workorder.siteid) and siteid = 'EEP')))
union
select 'CREW 220 b comp_close:',count(*)from workorder
WHERE status IN ('COMP','CLOSE') and (upper(crewid) = 'CREW 220' or upper(crewid) = 'CREW 21') and supervisor like 'OTTSTE%'
and reportdate >= to_timestamp ('2010-12-31 23:00:00' , 'YYYY-MM-DD HH24:MI:SS.FF') and istask = 'N' and worktype = 'PM'
and (exists (select siteid from maximo.locancestor where ((ancestor like 'FORT%'))
and (location =workorder.location and systemid = ( select primarysystem from site
where siteid = workorder.siteid) and siteid = 'EEP')))
1) The first part of the query, before the UNION -- supervisor like 'OTTSTE' -- is missing a percent sign wildcard (the second part of the query has one)
2) Probably not a big deal, but your to_timestamp's are asking for milliseconds and you've not provided them. I assume it will assume "000" so not a big deal (I'm not an Oracle user) :-(
3) The SITE table does not have an attribute/field called PRIMARYSYSTEM (at least not in 6.x that I work on, maybe it did in 5.x).
4) Is the first half of the query supposed to have a status filter?
5) You've told it to pick work orders from after 11:00 PM on New Year's Eve. . .any chance there are work orders that were created between 11:00 PM and midnight? That would throw the numbers off slightly, assuming your goal is to find work done in 2011.
I think someone else already responded to this post with advice of "do each part of the query separately and make sure it works before performing the union." Very good advice, that is.
Hopefully something here will help. . .
Travis Herron
--- In MAXIMO@yahoogroups.com, "Tima" <timawolff@...> wrote:
>
> I'm trying to run the query below from a program called Golden but it's not pulling the correct numbers from Maximo 5.2.
>
> Does anyone know what might be wrong with this?
>
>
> select 'CREW 220 a SO PM total:',count(*)
> from workorder WHERE (upper(crewid) = 'CREW 220' or upper(crewid) = 'CREW 21') and supervisor like 'OTTSTE'
> and reportdate >= to_timestamp ('2010-12-31 23:00:00' , 'YYYY-MM-DD HH24:MI:SS.FF') and istask = 'N' and worktype = 'PM'
> and (exists (select siteid from maximo.locancestor where ((ancestor like 'FORT%'))
> and (location =workorder.location and systemid = ( select primarysystem from site
> where siteid = workorder.siteid) and siteid = 'EEP')))
> union
> select 'CREW 220 b comp_close:',count(*)from workorder
> WHERE status IN ('COMP','CLOSE') and (upper(crewid) = 'CREW 220' or upper(crewid) = 'CREW 21') and supervisor like 'OTTSTE%'
> and reportdate >= to_timestamp ('2010-12-31 23:00:00' , 'YYYY-MM-DD HH24:MI:SS.FF') and istask = 'N' and worktype = 'PM'
> and (exists (select siteid from maximo.locancestor where ((ancestor like 'FORT%'))
> and (location =workorder.location and systemid = ( select primarysystem from site
> where siteid = workorder.siteid) and siteid = 'EEP')))
>