Databases
StackOverflow2013
Postsdbo
POQuery Help: A view that totals some groups
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuery Help: A view that totals some groups
    primarykey
    data
    text
    <p>I'm looking for the correct way to structure the query for a view in order to include some totaling, etc. on the DB side (the view will be used by reporting systems).</p> <h1>Relevant Data Structure</h1> <pre><code>+---------+ +---------+ |WORKORDER| |WPLABOR | |---------| |---------| |WONUM |+----&gt;|WONUM | |... | + |LABORHRS | +---------+ | |RATE | | +---------+ | | +---------+ | |WPITEM | | |---------| +--&gt;|WONUM | | |ITEMQTY | | |UNITCOST | | +---------+ | | +----------------+ | |LONGDESCRIPTION | | |----------------| +--&gt;|LDKEY | |LDTEXT | +----------------+ </code></pre> <h1>Goal</h1> <p>I would like to return the following:</p> <ul> <li>Various items from Workorder</li> <li>The sum of all labor costs (sum of each laborhrs*rate)</li> <li>The sum of all items (sum of each itemqty*unitcost)</li> <li>The LDText CLOB</li> </ul> <p>I'm to the point where I have the relevant information displayed in the query, but am struggling with the totaling of the labor and item costs. </p> <h1>Query So Far</h1> <pre><code> SELECT WORKORDER.WONUM, WORKORDER.ACTLABHRS, WORKORDER.LOCATION, WORKORDER.STATUS, WORKORDER.WO7, -- Requester WORKORDER.WO8, -- Extension WORKORDER.WO9, -- Location WORKORDER.LEADCRAFT, WORKORDER.WO11, -- Extension WORKORDER.GLACCOUNT, WORKORDER.WO10, -- Contact WORKORDER.DESCRIPTION, -- Short description WORKORDER.WO6, -- Plant rearrangement (YORN / boolean value) LONGDESCRIPTION.LDTEXT, WPLABOR.LABORHRS, WPLABOR.RATE, WPITEM.ITEMQTY, WPITEM.UNITCOST FROM MAXIMO.WORKORDER LEFT OUTER JOIN MAXIMO.LONGDESCRIPTION ON WORKORDER.WONUM = CAST(LONGDESCRIPTION.LDKEY as varchar(22)) LEFT OUTER JOIN MAXIMO.WPLABOR ON WORKORDER.WONUM = WPLABOR.WONUM LEFT OUTER JOIN MAXIMO.WPITEM ON WORKORDER.WONUM = WPITEM.WONUM WHERE LONGDESCRIPTION.LDOWNERTABLE='WORKORDER' AND LONGDESCRIPTION.LDOWNERCOL = 'DESCRIPTION'; </code></pre> <p>Thanks for any help you can give!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndrew
    1. USSeanKilleen
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POQuery Help: A view that totals some groups
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you clarify the design a bit. Does WPLabor contain one or many records that need to be "summed" and same for Item.
      singulars
      1. POQuery Help: A view that totals some groups
      1. USxQbert
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload