

5:41 pm

October 20, 2014

Hello,
I’m trying to create a basic table report in maximo which sums up labour and material costs on a workorder. Once I have the correct data I’ll grouped this by location in BIRT.
The issue im encountering at the moment is my SQL brings back duplicate records.
select locations.description as Station, LABTRANS.REGULARHRS AS Hours, LABTRANS.LINECOST, LABTRANS.TRANSTYPE, MATUSETRANS.UNITCOST, MATUSETRANS.DESCRIPTION as MATERIAL " + " from workorder " + " join locations on (WORKORDER.LOCATION=LOCATIONS.LOCATION) " + " join labtrans on (workorder.wonum=labtrans.refwo) " + " join MATUSETRANS on wonum=MATUSETRANS.refwo " + " where (workorder.worktype = 'CAS') and (wonum = 'AA40854') and (workorder.status IN ('CLOSED', 'HISTORY')) " ;
work order AA40854 has 4 rows of materials and 2 rows of labor (1 x WORK, 1x TRAV). The query above duplicates the labor records to 4 rows (2 x WORK, 2 x TRAV). This gives me a false total.
100% sure my lack of sql is holding this report back. Can someone point me in the right direction with this query so I retrieve my 4 rows of materials and 2 rows of labor.
Thanks.
3:29 pm

December 21, 2015

I agree with the previous poster. You will have to split this up into multiple queries. You’re running across basic database design where you are trying to create a one to many relationship. One work order/labor hours to many parts that is why you are getting the duplicates.
As a side note, you can try to use workorder.actmatcost and workorder.actlabhrs instead of trying to sum up your own material cost. (I am in 7.6 but I bet the older versions have that field). It would look like:
select workorder.wonum, workorder.actmatcost, workorder.actlabhrs, (workorder.actmatcost + workorder.actlabhrs) as total cost
from workorder
where workorder.worktype = ‘CAS’
and wonum = ‘AA40854’
and workorder.status IN (‘CLOSED’, ‘HISTORY’)
Most Users Ever Online: 54
Currently Online:
1 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
sun kim: 60
madd0g17: 59
v.barsamov: 51
cgregory: 45
Maximofresher: 45
matta0990: 33
Member Stats:
Guest Posters: 0
Members: 14366
Moderators: 0
Admins: 4
Forum Stats:
Groups: 1
Forums: 8
Topics: 1580
Posts: 4295
Newest Members:
Megan Morgan, Devendra Verma, stephen stephens, maxexpertise Hilal, Jeroen van Proosdij, Neeraja NAdministrators: Chon: 1118, kristensibilia: 0, Joe Grassia: 0, bpaik: 0