October 20, 2014
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.
October 20, 2014
November 1, 2015
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
where workorder.worktype = ‘CAS’
and wonum = ‘AA40854’
and workorder.status IN (‘CLOSED’, ‘HISTORY’)
Most Users Ever Online: 54
Currently Browsing this Page:
sun kim: 60
Guest Posters: 0
Newest Members:Megan Morgan, Devendra Verma, stephen stephens, maxexpertise Hilal, Jeroen van Proosdij, Neeraja N
Administrators: Chon: 1118, kristensibilia: 0, Joe Grassia: 0, bpaik: 0