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.
Wondering if someone could help me out with this query.
I'm trying to pull all work orders and this is only giving me repairs and call
out but I also need my PM and PMREG work orders.
Select distinct workorder.WONUM, workorder.worktype, workorder.location,
workorder.description,
Find_Value(ldkey,'WORKORDER','DESCRIPTION') description_longdesc,
workorder.wo4 as ACTION,Find_Value(ldkey,'WORKORDER','WO4') action_longdesc,
workorder.reportedby, workorder.jpnum, workorder.faildate,
workorder.failurecode,workorder.problemcode,
failurereport.Failurecode,workorder.REPORTdate, workorder.actfinish,
workorder.status,workorder.leadcraft
from workorder, failurereport,locancestor
where workorder.wonum = failurereport.wonum
and workorder.siteid = failurereport.siteid
and workorder.location = locancestor.location
and workorder.siteid = locancestor.siteid
and workorder.istask = 'N'
and workorder.siteid = 'EEP'
and workorder.location like 'PR-5-UP-%'
and workorder.reportdate <= to_timestamp ('2011-09-30 23:59:59', 'YYYY-MM-DD
HH24:MI:SS.FF')
and Workorder.reportdate >= to_timestamp ('2005-12-31 23:00:00', 'YYYY-MM-DD
HH24:MI:SS.FF')
and locancestor.ancestor = 'SUPERIOR REGION';
Thanks in advance!
Please use left outer join.
From: Tima Wolff <timawolff@yahoo.com>
To: MAXIMO@yahoogroups.com
Sent: Monday, September 19, 2011 1:49 PM
Subject: [MAXIMO List] 5.1 Query Help
Wondering if someone could help me out with this query.
I'm trying to pull all work orders and this is only giving me repairs and call
out but I also need my PM and PMREG work orders.
Select distinct workorder.WONUM, workorder.worktype, workorder.location,
workorder.description,
Find_Value(ldkey,'WORKORDER','DESCRIPTION') description_longdesc,
workorder.wo4 as ACTION,Find_Value(ldkey,'WORKORDER','WO4') action_longdesc,
workorder.reportedby, workorder.jpnum, workorder.faildate,
workorder.failurecode,workorder.problemcode,
failurereport.Failurecode,workorder.REPORTdate, workorder.actfinish,
workorder.status,workorder.leadcraft
from workorder, failurereport,locancestor
where workorder.wonum = failurereport.wonum
and workorder.siteid = failurereport.siteid
and workorder.location = locancestor.location
and workorder.siteid = locancestor.siteid
and workorder.istask = 'N'
and workorder.siteid = 'EEP'
and workorder.location like 'PR-5-UP-%'
and workorder.reportdate <= to_timestamp ('2011-09-30 23:59:59', 'YYYY-MM-DD
HH24:MI:SS.FF')
and Workorder.reportdate >= to_timestamp ('2005-12-31 23:00:00', 'YYYY-MM-DD
HH24:MI:SS.FF')
and locancestor.ancestor = 'SUPERIOR REGION';
Thanks in advance!
If you have any worktype criteria for differentiating between PM and PMREG workorder, you can add that to this query :
Select distinct workorder.WONUM, workorder.worktype, workorder.location,
workorder.description,
Find_Value(ldkey,'WORKORDER','DESCRIPTION') description_longdesc,
workorder.wo4 as ACTION,Find_Value(ldkey,'WORKORDER','WO4') action_longdesc,
workorder.reportedby, workorder.jpnum, workorder.faildate,
workorder.failurecode,workorder.problemcode,
failurereport.Failurecode,workorder.REPORTdate, workorder.actfinish,
workorder.status,workorder.leadcraft
from workorder left outer join failurereport
on workorder.wonum = failurereport.wonum
and workorder.siteid = failurereport.siteid
left outer join pm on pm.pmnum = workorder.pmnum and pm.siteid=workorder.siteid
left outer join locancestor on workorder.location = locancestor.location
and workorder.siteid = locancestor.siteid
where
workorder.istask = 'N'
and workorder.siteid = 'EEP'
and workorder.location like 'PR-5-UP-%'
and workorder.reportdate <= to_timestamp ('2011-09-30 23:59:59', 'YYYY-MM-DD
HH24:MI:SS.FF')
and Workorder.reportdate >= to_timestamp ('2005-12-31 23:00:00', 'YYYY-MM-DD
HH24:MI:SS.FF')
and locancestor.ancestor = 'SUPERIOR REGION';
Thanks,
Victor.
--- In MAXIMO@yahoogroups.com, Tima Wolff <timawolff@...> wrote:
>
> Wondering if someone could help me out with this query.
> I'm trying to pull all work orders and this is only giving me repairs and call
> out but I also need my PM and PMREG work orders.
>
>
> Select distinct workorder.WONUM, workorder.worktype, workorder.location,
> workorder.description,
> Find_Value(ldkey,'WORKORDER','DESCRIPTION') description_longdesc,
> workorder.wo4 as ACTION,Find_Value(ldkey,'WORKORDER','WO4') action_longdesc,
> workorder.reportedby, workorder.jpnum, workorder.faildate,
> workorder.failurecode,workorder.problemcode,
> failurereport.Failurecode,workorder.REPORTdate, workorder.actfinish,
> workorder.status,workorder.leadcraft
> from workorder, failurereport,locancestor
> where workorder.wonum = failurereport.wonum
> and workorder.siteid = failurereport.siteid
> and workorder.location = locancestor.location
> and workorder.siteid = locancestor.siteid
> and workorder.istask = 'N'
> and workorder.siteid = 'EEP'
> and workorder.location like 'PR-5-UP-%'
> and workorder.reportdate <= to_timestamp ('2011-09-30 23:59:59', 'YYYY-MM-DD
> HH24:MI:SS.FF')
> and Workorder.reportdate >= to_timestamp ('2005-12-31 23:00:00', 'YYYY-MM-DD
> HH24:MI:SS.FF')
> and locancestor.ancestor = 'SUPERIOR REGION';
>
> Thanks in advance!
>
>
>